from rest_framework.response import Response
from rest_framework import status
from rest_framework.viewsets import ModelViewSet
import pymysql, xlrd, datetime, requests, hashlib, json
from django.conf import settings
from django.http import StreamingHttpResponse
from django.utils.encoding import escape_uri_path
from django.db.models import Q, F, Func
from django.db import transaction

from equity_admin.serializers_merchants import *
from equity_mall.utils.pages import MyPage
from equity_mall.utils.utils import get_level_list, get_sign
from equity_admin.serializers import LevelListSerializer
from equity_mall.utils.qiniu_storage import pic_upload
from equity_admin.tasks import *
from equity_mall.utils.wash_level1 import ylx_api


class MerchantUserModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    filter_class = MerchantUserFilter
    search_fields = ('mobile', )

    def get_queryset(self):
        queryset = MerchantUser.objects.all()
        return queryset.order_by('-id')

    def get_serializer_class(self):
        if self.action == 'list':
            return MerchantUserListModelSerializer
        else:
            return MerchantUserRetrieveModelSerializer

    def merchant_data(self, request):
        x_forwarded_for = request.META.get('HTTP_X_FORWARDED_FOR')
        if x_forwarded_for:
            ip = x_forwarded_for.split(',')[0]
        else:
            ip = request.META.get('REMOTE_ADDR')
        print(ip)
        if ip not in ['14.145.29.135', '175.178.237.77', '192.168.2.100']:
            return Response({'msg': '无效白名单ip', 'state': 400, 'data': {}}, status=status.HTTP_200_OK)
        openid = self.request.query_params.get('openid', None)
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        sql = f'''SELECT a.*,b.acceptId FROM (SELECT wyfMerchantID,name,store_address,latitude,longitude,level_id FROM tb_merchant WHERE wyfMerchantID="{openid}")AS a LEFT JOIN (SELECT id,acceptId FROM tb_u_level)AS b ON a.level_id=b.id;'''
        cursor.execute(sql)
        row = cursor.fetchone()
        cursor.close()
        conn.close()
        response_data = {}
        if row:
            response_data['merchant_name'] = row['name']
            response_data['openid'] = row['wyfMerchantID']
            response_data['address'] = row['store_address']
            response_data['latitude'] = row['latitude']
            response_data['longitude'] = row['longitude']
            response_data['org_id'] = row['acceptId']
        else:
            business_no = openid
            md5 = hashlib.md5()
            md5.update(str(business_no).encode('utf-8'))
            shop_id = md5.hexdigest()
            url = 'https://manage.gdwxyf.com/internal/searchmerchantbyshopids.do'
            body = {
                'account': 'gdyouzheng',
                'shopId': shop_id
            }
            reaponse = requests.post(url=url, data=body)
            try:
                result = reaponse.json()
            except:
                return Response({'msg': '系统繁忙', 'state': 400, 'data': {}}, status=status.HTTP_200_OK)
            if'shopNickname' not in result:
                return Response({'msg': '商户号不存在', 'state': 400, 'data': {}}, status=status.HTTP_200_OK)
            response_data['merchant_name'] = result['shopNickname']
            response_data['openid'] = openid
            response_data['address'] = result['address']
            response_data['latitude'] = result['latitude']
            response_data['longitude'] = result['longitude']
            response_data['org_id'] = result['accept_id']
        return Response({'msg': '成功', 'state': 200, 'data': response_data}, status=status.HTTP_200_OK)


class LuckyBagActivityModelViewSet(ModelViewSet):

    pagination_class = MyPage
    queryset = LuckyBagActivity.objects.all().order_by('-id')

    def get_serializer_class(self):
        if self.action == 'list':
            return LuckyBagActivityListModelSerializer
        else:
            return LuckyBagActivityRetrieveModelSerializer


class LuckyBagChildActivityDataModelViewSet(ModelViewSet):

    pagination_class = MyPage
    # queryset = LuckyBagChildActivityData.objects.all().order_by('-id')
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    filter_class = LuckyBagChildActivityDataFilter
    search_fields = ('business_no', 'merchantuser__mobile')

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = LuckyBagChildActivityData.objects.all()
        elif level.level == 1:
            queryset = LuckyBagChildActivityData.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = LuckyBagChildActivityData.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = LuckyBagChildActivityData.objects.filter(level__district_id=level.id)
        else:
            queryset = LuckyBagChildActivityData.objects.filter(level__branch_id=level.id)
        return queryset.order_by('-id')

    def get_serializer_class(self):
        if self.action == 'list':
            return LuckyBagChildActivityDataListModelSerializer
        else:
            return LuckyBagChildActivityDataRetrieveModelSerializer

    def export_luckyactivitydata(self, request):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        lucky_status = self.request.query_params.get('lucky_status', None)
        luckybagchildactivity_id = self.request.query_params.get('luckybagchildactivity_id', None)
        merchantuser_id = self.request.query_params.get('merchantuser_id', None)
        lucky_bag_max = self.request.query_params.get('lucky_bag_max', None)
        lucky_bag_min = self.request.query_params.get('lucky_bag_min', None)
        received_time_begin = self.request.query_params.get('received_time_begin', None)
        received_time_end = self.request.query_params.get('received_time_end', None)
        search = self.request.query_params.get('search', None)
        if level.level == 0:
            condition = 'WHERE id!=0'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if search:
            condition = condition + f' AND (business_no LIKE "%{search}%")'
        if lucky_status:
            condition = condition + f' AND lucky_status IN ({lucky_status})'
        if received_time_begin:
            condition = condition + f' AND received_time >= "{received_time_begin + " 00:00:00"}"'
        if received_time_end:
            condition = condition + f' AND received_time <= "{received_time_end + " 23:59:59"}"'
        if lucky_bag_max:
            condition = condition + f' AND lucky_bag <= {lucky_bag_max}'
        if lucky_bag_min:
            condition = condition + f' AND lucky_bag >= {lucky_bag_min}'
        if luckybagchildactivity_id:
            condition = condition + f' AND luckybagchildactivity_id IN ({luckybagchildactivity_id})'
        if merchantuser_id:
            condition = condition + f' AND merchantuser_id IN ({merchantuser_id})'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT g.*,h.mobile FROM (SELECT e.*,f.activity_type FROM (SELECT c.*,d.city_name,district_name,branch_name,account FROM (SELECT * FROM (SELECT id,business_no,lucky_bag,lucky_status,luckybagchildactivity_id,level_id,DATE_FORMAT(received_time,'%Y-%m-%d %H:%i:%S') AS received_time,merchantuser_id FROM tb_lucky_bag_child_activity_data {condition})AS a LEFT JOIN (SELECT wyfMerchantID,name,short_name FROM tb_merchant)AS b ON a.business_no=b.wyfMerchantID)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name,account FROM tb_u_level)AS d ON c.level_id=d.id)AS e LEFT JOIN (SELECT id,activity_type FROM tb_lucky_bag_child_activity)AS f ON e.luckybagchildactivity_id=f.id)AS g LEFT JOIN (SELECT id,mobile FROM tb_m_merchant_user)AS h ON g.merchantuser_id=h.id;
            '''
            cursor.execute(sql)
        except Exception as e:
            sql = f'''
            SELECT g.*,h.mobile FROM (SELECT e.*,f.activity_type FROM (SELECT c.*,d.city_name,district_name,branch_name,account FROM (SELECT * FROM (SELECT id,business_no,lucky_bag,lucky_status,luckybagchildactivity_id,level_id,DATE_FORMAT(received_time,'%Y-%m-%d %H:%i:%S') AS received_time,merchantuser_id FROM tb_lucky_bag_child_activity_data)AS a LEFT JOIN (SELECT wyfMerchantID,name,short_name FROM tb_merchant)AS b ON a.business_no=b.wyfMerchantID)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name,account FROM tb_u_level)AS d ON c.level_id=d.id)AS e LEFT JOIN (SELECT id,activity_type FROM tb_lucky_bag_child_activity)AS f ON e.luckybagchildactivity_id=f.id)AS g LEFT JOIN (SELECT id,mobile FROM tb_m_merchant_user)AS h ON g.merchantuser_id=h.id;
            '''
            cursor.execute(sql)
        ziduan = ['id', '微邮付商户号', '商户简称', '商户全称', '网点机构号', '所属市公司', '所属区县', '所属网点', '领取状态', '领取福袋数', '领取时间', '活动类型', '领取人手机号']
        cols = ['id', 'wyfMerchantID', 'short_name', 'name', 'account', 'city_name', 'district_name', 'branch_name', 'lucky_status', 'lucky_bag', 'received_time', 'activity_type', 'mobile']
        response = StreamingHttpResponse(self.get_luckyactivitydata_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('88节福袋领取记录.csv'))
        return response

    def get_luckyactivitydata_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        LUCKY_STATUS_ENUM = {
            0: '待抽奖',
            1: '待领取',
            2: '已领取',
            3: '已兑换',
            4: '已过期'
        }
        ACTIVITY_TYPE_ENUM = {
            1: '日日消费礼',
            2: '周周榜单王',
            3: '支付宝大额交易抽奖',
            4: '国庆7天乐'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col in ['ryx_smid', 'sxf_smid']:
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                elif col == 'lucky_status':
                    tmp_str += '"%s",' % (LUCKY_STATUS_ENUM[row[col]])
                elif col == 'activity_type':
                    tmp_str += '"%s",' % (ACTIVITY_TYPE_ENUM[row[col]])
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class LuckyBagRecordModelViewSet(ModelViewSet):

    pagination_class = MyPage
    queryset = LuckyBagRecord.objects.filter(id__gt=28).order_by('-id')
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    filter_class = LuckyBagRecordFilter
    search_fields = ('merchantuser__mobile', )

    def get_serializer_class(self):
        if self.action == 'list':
            return LuckyBagRecordListModelSerializer
        else:
            return LuckyBagRecordRetrieveModelSerializer

    def export_luckyrecord(self, request):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        operate_type = self.request.query_params.get('operate_type', None)
        merchantuser_id = self.request.query_params.get('merchantuser_id', None)
        lucky_bag_max = self.request.query_params.get('lucky_bag_max', None)
        lucky_bag_min = self.request.query_params.get('lucky_bag_min', None)
        create_time_begin = self.request.query_params.get('create_time_begin', None)
        create_time_end = self.request.query_params.get('create_time_end', None)
        search = self.request.query_params.get('search', None)
        if level.level == 0:
            condition = 'WHERE id>28'
        elif level.level == 1:
            condition = f'WHERE id>28 AND level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE id>28 AND level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE id>28 AND level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE id>28 AND level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if search:
            condition = condition + f' AND (business_no LIKE "%{search}%")'
        if operate_type:
            condition = condition + f' AND operate_type IN ({operate_type})'
        if create_time_begin:
            condition = condition + f' AND create_time >= "{create_time_begin + " 00:00:00"}"'
        if create_time_end:
            condition = condition + f' AND create_time <= "{create_time_end + " 23:59:59"}"'
        if lucky_bag_max:
            condition = condition + f' AND lucky_bag <= {lucky_bag_max}'
        if lucky_bag_min:
            condition = condition + f' AND lucky_bag >= {lucky_bag_min}'
        if merchantuser_id:
            condition = condition + f' AND merchantuser_id IN ({merchantuser_id})'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT k.*,l.merchant_name1,wyfMerchantID FROM (SELECT i.*,j.account,city_name,district_name,branch_name FROM (SELECT g.*,h.merchant_name FROM (SELECT e.*,f.activity_type FROM (SELECT c.*,d.business_no,luckybagchildactivity_id FROM (SELECT a.*,b.mobile,level_id FROM (SELECT id,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S') AS create_time,operate_type,lucky_bag,activitydata_id,cashactivitydata_id,merchantuser_id FROM tb_lucky_bag_record {condition})AS a LEFT JOIN (SELECT id,mobile,level_id FROM tb_m_merchant_user)AS b ON a.merchantuser_id=b.id)AS c LEFT JOIN (SELECT id,luckybagchildactivity_id,business_no FROM tb_lucky_bag_child_activity_data)AS d ON c.activitydata_id=d.id)AS e LEFT JOIN (SELECT id,activity_type FROM tb_lucky_bag_child_activity)AS f ON e.luckybagchildactivity_id=f.id)AS g LEFT JOIN (SELECT wyfMerchantID,name AS merchant_name FROM tb_merchant)AS h ON g.business_no=h.wyfMerchantID)AS i LEFT JOIN (SELECT id,city_name,district_name,branch_name,account FROM tb_u_level)AS j ON i.level_id=j.id)AS k LEFT JOIN (SELECT merchantuser_id,name AS merchant_name1,wyfMerchantID FROM tb_merchant)AS l ON k.merchantuser_id=l.merchantuser_id;
            '''
            cursor.execute(sql)
        except Exception as e:
            sql = f'''
            SELECT k.*,l.merchant_name1,wyfMerchantID FROM (SELECT i.*,j.account,city_name,district_name,branch_name FROM (SELECT g.*,h.merchant_name FROM (SELECT e.*,f.activity_type FROM (SELECT c.*,d.business_no,luckybagchildactivity_id FROM (SELECT a.*,b.mobile,level_id FROM (SELECT id,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S') AS create_time,operate_type,lucky_bag,activitydata_id,cashactivitydata_id,merchantuser_id FROM tb_lucky_bag_record)AS a LEFT JOIN (SELECT id,mobile,level_id FROM tb_m_merchant_user)AS b ON a.merchantuser_id=b.id)AS c LEFT JOIN (SELECT id,luckybagchildactivity_id,business_no FROM tb_lucky_bag_child_activity_data)AS d ON c.activitydata_id=d.id)AS e LEFT JOIN (SELECT id,activity_type FROM tb_lucky_bag_child_activity)AS f ON e.luckybagchildactivity_id=f.id)AS g LEFT JOIN (SELECT wyfMerchantID,name AS merchant_name FROM tb_merchant)AS h ON g.business_no=h.wyfMerchantID)AS i LEFT JOIN (SELECT id,city_name,district_name,branch_name,account FROM tb_u_level)AS j ON i.level_id=j.id)AS k LEFT JOIN (SELECT merchantuser_id,name AS merchant_name1,wyfMerchantID FROM tb_merchant)AS l ON k.merchantuser_id=l.merchantuser_id;
            '''
            cursor.execute(sql)
        ziduan = ['id', '微邮付商户号', '商户全称', '网点机构号', '所属市公司', '所属区县', '所属网点', '类型', '领取福袋数', '领取时间', '活动类型', '领取人手机号']
        cols = ['id', 'business_no', 'merchant_name', 'account', 'city_name', 'district_name', 'branch_name', 'operate_type', 'lucky_bag', 'create_time', 'activity_type', 'mobile']
        response = StreamingHttpResponse(self.get_luckyrecord_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('88节福袋领取记录.csv'))
        return response

    def get_luckyrecord_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        OPERATE_TYPE_ENUM = {
            1: '抽奖',
            2: '提现'
        }
        ACTIVITY_TYPE_ENUM = {
            1: '日日消费礼',
            2: '周周榜单王',
            3: '支付宝大额交易抽奖'
        }
        distinct_id = []
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            if row['id'] in distinct_id:
                continue
            distinct_id.append(row['id'])
            for col in cols:
                if col in ['ryx_smid', 'sxf_smid']:
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                elif col == 'operate_type':
                    tmp_str += '"%s",' % (OPERATE_TYPE_ENUM[row[col]])
                elif col == 'activity_type':
                    tmp_str += '"%s",' % (ACTIVITY_TYPE_ENUM[row[col]] if row[col] in ACTIVITY_TYPE_ENUM else "")
                elif col == 'business_no':
                    if row['operate_type'] == 1:
                        tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                    else:
                        tmp_str += '"%s",' % (str(row['wyfMerchantID']).encode('GBK', 'ignore').decode('gbk') if row['wyfMerchantID'] else '')
                elif col == 'merchant_name':
                    if row['operate_type'] == 1:
                        tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                    else:
                        tmp_str += '"%s",' % (str(row['merchant_name1']).encode('GBK', 'ignore').decode('gbk') if row['merchant_name1'] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class MerchantEnterQuestModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = MerchantEnterQuestFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    serializer_class = MerchantEnterQuestModelSerializer

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = MerchantEnterQuest.objects.all()
        elif level.level == 1:
            queryset = MerchantEnterQuest.objects.filter(create_user__level__prov_id=level.id)
        elif level.level == 2:
            queryset = MerchantEnterQuest.objects.filter(create_user__level__city_id=level.id)
        elif level.level == 3:
            queryset = MerchantEnterQuest.objects.filter(create_user__level__district_id=level.id)
        else:
            queryset = MerchantEnterQuest.objects.filter(create_user__level__branch_id=level.id)
        return queryset.order_by('-id')


class ScoutTheStoreLeaderboardModelViewSet(ModelViewSet):

    queryset = ScoutTheStoreLeaderboard.objects.all().order_by('-likes')
    serializer_class = ScoutTheStoreLeaderboardModelSerializer

    def create(self, request, *args, **kwargs):
        my_file = self.request.FILES.get('my_file', None)
        if not my_file:
            return Response({'error': '请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)

        type_excel = my_file.name.split('.')[-1]
        file_contents = my_file.read()
        if type_excel not in ['xlsx', 'xls']:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            wb = xlrd.open_workbook(filename=None, file_contents=file_contents)  # 关键点在于这里
        except:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        table = wb.sheets()[0]
        nrows = table.nrows
        title = table.row_values(0)
        if '地市' not in title:
            return Response({'error': '表格字段缺少"地市"'}, status=status.HTTP_400_BAD_REQUEST)
        if '商户' not in title:
            return Response({'error': '表格字段缺少"商户"'}, status=status.HTTP_400_BAD_REQUEST)
        if '点赞数' not in title:
            return Response({'error': '表格字段缺少"点赞数"'}, status=status.HTTP_400_BAD_REQUEST)
        if '创作者' not in title:
            return Response({'error': '表格字段缺少"创作者"'}, status=status.HTTP_400_BAD_REQUEST)
        if '截止时间' not in title:
            return Response({'error': '表格字段缺少"截止时间"'}, status=status.HTTP_400_BAD_REQUEST)
        city_index = title.index('地市')
        merhant_name_index = title.index('商户')
        likes_index = title.index('点赞数')
        creator_index = title.index('创作者')
        update_time_index = title.index('截止时间')
        update_time = str(table.row_values(1)[update_time_index])
        try:
            update_time = datetime.datetime.strptime(update_time, '%Y年%m月%d日%H:%M:%S').strftime("%Y-%m-%d %H:%M:%S")
        except:
            return Response({'error': '时间格式有误'}, status=status.HTTP_400_BAD_REQUEST)
        ScoutTheStoreLeaderboard.objects.all().delete()
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        sql = 'INSERT INTO tb_scout_the_store_leaderboard(city,merhant_name,likes,update_time,creator) VALUES'
        try:
            for i in range(1, nrows):
                rowValues = table.row_values(i)
                city = str(rowValues[city_index]).replace('.0', '').replace(' ', '')
                merhant_name = str(rowValues[merhant_name_index]).replace('.0', '').replace(' ', '')
                likes = str(rowValues[likes_index]).replace('.0', '').replace(' ', '')
                creator = str(rowValues[creator_index]).replace('.0', '').replace(' ', '').split('、')
                insert_data = []
                insert_data.append(city)
                insert_data.append(merhant_name)
                insert_data.append(likes)
                insert_data.append(update_time)
                insert_data.append(str(creator))
                sql += str(tuple(insert_data)).replace("'null'", 'null')
                sql += ','
            sql = sql[:-1]
            sql += ';'
            # print(sql)
            cursor.execute(sql)
            conn.commit()
        except Exception as e:
            print(e)
            conn.rollback()
            cursor.close()
            conn.close()
            return Response({'error': '文件格式有误'}, status=status.HTTP_400_BAD_REQUEST)
        cursor.close()
        conn.close()
        return Response({'success': '更新排行榜成功'}, status=status.HTTP_200_OK)


class MerchantNFCDeviceTradeModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = MerchantNFCDeviceTradeFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    serializer_class = MerchantNFCDeviceTradeModelSerializer
    search_fields = ('merchant__wyfMerchantID', 'merchant__name', 'sn', 'transaction_id')

    def get_queryset(self):
        queryset = MerchantNFCDeviceTrade.objects.all()
        activity_id = self.request.query_params.get('activity_id', None)
        is_bind = self.request.query_params.get('is_bind', None)
        if activity_id:
            activity = MerchantEightEightActivity.objects.filter(id=activity_id).first()
            if activity:
                trade_time__gte = (activity.activity_start_time + datetime.timedelta(days=-7)).strftime("%Y-%m-%d 00:00:00")
                trade_time__lte = (activity.activity_end_time + datetime.timedelta(days=-7)).strftime("%Y-%m-%d 23:59:59")
                queryset = queryset.filter(trade_time__gte=trade_time__gte, trade_time__lte=trade_time__lte)
        if is_bind == '1':
            queryset = queryset.filter(merchant_id__isnull=False)
        elif is_bind == '2':
            queryset = queryset.filter(merchant_id__isnull=True)
        return queryset.order_by('-trade_time')

    def export_nfc_trade(self, request):
        activity_id = self.request.query_params.get('activity_id', None)
        is_bind = self.request.query_params.get('is_bind', None)
        trade_time_begin = self.request.query_params.get('trade_time_begin', None)
        trade_time_end = self.request.query_params.get('trade_time_end', None)
        total_amount_max = self.request.query_params.get('total_amount_max', None)
        total_amount_min = self.request.query_params.get('total_amount_min', None)
        search = self.request.query_params.get('search', None)
        device_id = self.request.query_params.get('device_id', None)
        condition = 'WHERE id!=3750'
        if trade_time_begin:
            condition = condition + f' AND trade_time >= "{trade_time_begin + " 00:00:00"}"'
        if trade_time_end:
            condition = condition + f' AND trade_time <= "{trade_time_end + " 23:59:59"}"'
        if total_amount_min:
            condition = condition + f' AND total_amount >= "{total_amount_min}"'
        if total_amount_max:
            condition = condition + f' AND total_amount <= "{total_amount_max}"'
        if device_id:
            condition = condition + f' AND device_id={device_id}'
        if activity_id:
            activity = MerchantEightEightActivity.objects.filter(id=activity_id).first()
            if activity:
                trade_time__gte = (activity.activity_start_time + datetime.timedelta(days=-7)).strftime("%Y-%m-%d 00:00:00")
                trade_time__lte = (activity.activity_end_time + datetime.timedelta(days=-7)).strftime("%Y-%m-%d 23:59:59")
                condition = condition + f' AND trade_time >= "{trade_time__gte}"'
                condition = condition + f' AND trade_time <= "{trade_time__lte}"'
        if is_bind == '1':
            condition = condition + f' AND merchant_id IS NOT NULL'
        elif is_bind == '2':
            condition = condition + f' AND merchant_id IS NULL'
        if search:
            condition = condition + f' AND (merchant_id IN (SELECT id FROM tb_merchant WHERE name LIKE "%{search}%" OR wyfMerchantID LIKE "%{search}%") OR sn LIKE "%{search}%" OR transaction_id LIKE "%{search}%")'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT c.*,d.city_name,district_name,branch_name FROM (SELECT a.*,b.name,wyfMerchantID FROM (SELECT *,DATE_FORMAT(trade_time,'%Y-%m-%d %H:%i:%S') AS trade_time1 FROM tb_merchant_nfc_device_trade {condition})AS a LEFT JOIN (SELECT id,name,wyfMerchantID FROM tb_merchant)AS b ON a.merchant_id=b.id)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id;
            '''
            cursor.execute(sql)
        except:
            sql = f'''
            SELECT c.*,d.city_name,district_name,branch_name FROM (SELECT a.*,b.name,wyfMerchantID FROM (SELECT *,DATE_FORMAT(trade_time,'%Y-%m-%d %H:%i:%S') AS trade_time1 FROM tb_merchant_nfc_device_trade)AS a LEFT JOIN (SELECT id,name,wyfMerchantID FROM tb_merchant)AS b ON a.merchant_id=b.id)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id;
            '''
            cursor.execute(sql)
        ziduan = ['id', 'SN', '设备类型', '门店名称', '交易号', '交易金额', '交易时间', '微邮付商户号', '商户名称', '归属市公司', '归属区县', '归属网点', '交易类型']
        cols = ['id', 'sn', 'device_type', 'store_name', 'transaction_id', 'total_amount', 'trade_time1', 'wyfMerchantID', 'name', 'city_name', 'district_name', 'branch_name', 'pay_type']
        response = StreamingHttpResponse(self.get_nfc_trade_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('结算单信息.csv'))
        return response

    def get_nfc_trade_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col in ['total_amount']:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] != None else '-')
                elif col == 'transaction_id':
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                elif col == 'pay_type':
                    if row['pay_type'] == 1:
                        tmp_str += '"%s",' % '碰一下'
                    else:
                        tmp_str += '"%s",' % '扫码'
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class MerchantModelViewSet(ModelViewSet):
    '''商户'''

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('name', 'wyfMerchantID', 'managerName', 'managermobile')
    filter_class = MerchantFilter

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = Merchant.objects.all().order_by('-create_time')
        elif level.level == 1:
            queryset = Merchant.objects.filter(level__prov_id=level.id).order_by('-create_time')
        elif level.level == 2:
            queryset = Merchant.objects.filter(level__city_id=level.id).order_by('-create_time')
        elif level.level == 3:
            queryset = Merchant.objects.filter(level__district_id=level.id).order_by('-create_time')
        else:
            queryset = Merchant.objects.filter(level__branch_id=level.id).order_by('-create_time')
        return queryset

    def get_serializer_class(self):
        if self.action == 'list':
            return MerchantListModelSerializer
        else:
            return MerchantRetrieveModelSerializer

    def branch_info(self, request):
        merchant_id = self.request.query_params.get('merchant_id', None)
        if not merchant_id:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            instance = self.get_queryset().get(id=merchant_id)
        except:
            return Response({'error': '无效参数'}, status=status.HTTP_400_BAD_REQUEST)
        queryset = instance.merchantenter_set.all()
        page = self.paginate_queryset(queryset)
        data = MerchantEnterModelSerializer(page, many=True).data
        return self.get_paginated_response(data)

    def goods_info(self, request):
        merchant_id = self.request.query_params.get('merchant_id', None)
        if not merchant_id:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            instance = self.get_queryset().get(id=merchant_id)
        except:
            return Response({'error': '无效参数'}, status=status.HTTP_400_BAD_REQUEST)
        queryset = instance.goods_set.all()
        page = self.paginate_queryset(queryset)
        data = GoodsListModelSerializer(page, many=True).data
        return self.get_paginated_response(data)

    def check_merchant(self, request):
        user = self.request.iser
        business_no = self.request.data.get('business_no', None)
        if not business_no:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        business_no = str(business_no).replace(' ', '')
        if not business_no.isdigit() and len(business_no) != 10:
            return Response({'error': '请输入10位纯数字微邮付商户号'}, status=status.HTTP_400_BAD_REQUEST)
        md5 = hashlib.md5()  # 使用MD5加密模式
        md5.update(str(business_no).encode('utf-8'))  # 将参数字符串传入
        sign = md5.hexdigest()  # 完成加密并转为大写
        url = 'https://manage.gdwxyf.com/internal/searchmerchantbyshopids.do'
        body = {
            'account': 'gdyouzheng',
            'shopId': sign
        }
        reaponse = requests.post(url=url, data=body)
        try:
            reaponse = reaponse.json()
        except:
            return Response({'error': '系统繁忙,请重新操作'}, status=status.HTTP_400_BAD_REQUEST)
        if 'wx_channel_merchant_id' not in reaponse:
            return Response({'error': '商户号有误,请检查重新操作'}, status=status.HTTP_400_BAD_REQUEST)
        if reaponse['wx_channel_merchant_id'] == '':
            return Response({'error': '获取商户信息失败，请确认该商户是否审核成功。'}, status=status.HTTP_400_BAD_REQUEST)
        merchant = Merchant.objects.filter(wyfMerchantID=business_no).first()
        if merchant:
            merchantchannel_data = {
                'merchant_id': merchant.id
            }
            if reaponse['allchannelJson']['channel_ruiyinxin']:
                if reaponse['allchannelJson']['channel_ruiyinxin']['status'] == 1:
                    merchantchannel_data['smid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_4']
                    merchantchannel_data['channel_mchid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_7']
                    merchantchannel_data['channel_id'] = 2
                    merchantchannel_data['sub_mchid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_3']
                    MerchantChannel.objects.get_or_create(defaults=merchantchannel_data, merchant_id=merchant.id, channel_id=merchantchannel_data['channel_id'])
            if reaponse['allchannelJson']['channel_sxf_tq']:
                if reaponse['allchannelJson']['channel_sxf_tq']['status'] == 1:
                    merchantchannel_data['smid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_other_4'] if 'merchant_other_4' in reaponse['allchannelJson']['channel_sxf_tq'] else ''
                    merchantchannel_data['channel_mchid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_no']
                    merchantchannel_data['channel_id'] = 3
                    merchantchannel_data['sub_mchid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_other_3']
                    MerchantChannel.objects.get_or_create(defaults=merchantchannel_data, merchant_id=merchant.id, channel_id=merchantchannel_data['channel_id'])
            if reaponse['channel_mch_id'] == '227823797':
                try:
                    channel_url = 'https://manage.gdwxyf.com/internal/searchmerchantchannelmsg.do'
                    channel_body = {"account": "gdyouzheng", "shopId": sign}
                    channel_response = requests.post(url=channel_url, data=channel_body)
                    channel_result = channel_response.json()
                    merchantchannel_data['smid'] = channel_result['alipaychannelJson']['merchant_other_4'] if 'merchant_other_4' in channel_result['alipaychannelJson'] else ''
                    merchantchannel_data['channel_mchid'] = channel_result['alipaychannelJson']['upMerchantNo']
                    merchantchannel_data['channel_id'] = 15
                    merchantchannel_data['sub_mchid'] = channel_result['wxchannelJson']['merchant_other_3']
                    MerchantChannel.objects.get_or_create(defaults=merchantchannel_data, merchant_id=merchant.id, channel_id=merchantchannel_data['channel_id'])
                except:
                    pass
        else:
            with transaction.atomic():
                # 创建事务保存点
                save_id = transaction.savepoint()
                try:
                    merchant_data = {}
                    channel_mch_id = reaponse['channel_mch_id']
                    channel = Channel.objects.filter(mchid=channel_mch_id).first()
                    if channel:
                        merchant_data['channel_id'] = channel.id
                    merchant_data['name'] = reaponse['shopName']
                    merchant_data['short_name'] = reaponse['shopNickname']
                    merchant_data['wyfMerchantID'] = business_no
                    merchant_data['ruiyinxinMerchantID'] = sign
                    merchant_data['create_time'] = datetime.datetime.now()
                    if reaponse['businessType'] == '1':
                        merchant_data['subject_type'] = 3
                    elif reaponse['businessType'] == '2':
                        merchant_data['subject_type'] = 2
                    elif reaponse['businessType'] == '3':
                        merchant_data['subject_type'] = 1
                    else:
                        merchant_data['subject_type'] = 4
                    level = Level.objects.filter(acceptId=reaponse['accept_id']).first()
                    if level:
                        merchant_data['level_id'] = level.id
                    merchantuser, create = MerchantUser.objects.get_or_create(mobile=reaponse['phone'], defaults={"mobile": reaponse['phone'], 'level_id': level.id if level else None})
                    merchant_data['merchantuser_id'] = merchantuser.id
                    merchant_data['account_name'] = reaponse['cardName']
                    merchant_data['account_bank'] = reaponse['bankName']
                    merchant_data['account_number'] = reaponse['card']
                    if 'latitude' in reaponse:
                        merchant_data['latitude'] = reaponse['latitude']
                    if 'longitude' in reaponse:
                        merchant_data['longitude'] = reaponse['longitude']
                    card_copy = f'youhuiquanyi/{business_no}/card_copy.png'
                    card_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/cardFace.png', key=card_copy)
                    if card_copy_url:
                        merchant_data['card_copy'] = card_copy
                    merchant_data['identification_number'] = reaponse['identity']
                    merchant_data['store_address'] = reaponse['address']
                    store_header_copy = f'youhuiquanyi/{business_no}/store_header_copy.png'
                    store_header_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/merchantHead.png', key=store_header_copy)
                    if store_header_copy_url:
                        merchant_data['store_header_copy'] = store_header_copy
                    merchant, cereate = Merchant.objects.update_or_create(defaults=merchant_data, wyfMerchantID=business_no)
                    if not merchant.managerName:
                        merchant.managerName = reaponse['keeper']
                    if not merchant.managermobile:
                        merchant.managermobile = reaponse['phone']
                    if not merchant.prov_name:
                        merchant.prov_name = reaponse['province']
                        merchant.city_name = reaponse['city']
                        merchant.district_name = reaponse['area']
                    if not merchant.business_category:
                        merchant.business_category_id = 1
                    if not merchant.store_logo_copy:
                        merchant.store_logo_copy = 'default_merchant_logo.jpg'
                    merchant.save()
                    if cereate and merchant.channel:
                        store_header_copy = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                        store_header_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/merchantHead.png', key=store_header_copy)
                        if store_header_copy_url:
                            MerchantImage.objects.create(merchant_id=merchant.id, image=store_header_copy)
                        store_indoor_copy = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                        store_indoor_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/otherPhoto3.png', key=store_indoor_copy)
                        if store_indoor_copy_url:
                            MerchantImage.objects.create(merchant_id=merchant.id, image=store_indoor_copy)
                        merchantCheck = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                        merchantCheck_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/merchantCheck.png', key=merchantCheck)
                        if merchantCheck_url:
                            MerchantImage.objects.create(merchant_id=merchant.id, image=merchantCheck)
                    merchantchannel_data = {
                        'merchant_id': merchant.id
                    }
                    if reaponse['allchannelJson']['channel_ruiyinxin']:
                        if reaponse['allchannelJson']['channel_ruiyinxin']['status'] == 1:
                            merchantchannel_data['smid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_4']
                            merchantchannel_data['channel_mchid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_7']
                            merchantchannel_data['channel_id'] = 2
                            merchantchannel_data['sub_mchid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_3']
                            MerchantChannel.objects.get_or_create(defaults=merchantchannel_data, merchant_id=merchant.id, channel_id=merchantchannel_data['channel_id'])
                    if reaponse['allchannelJson']['channel_sxf_tq']:
                        if reaponse['allchannelJson']['channel_sxf_tq']['status'] == 1:
                            merchantchannel_data['smid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_other_4'] if 'merchant_other_4' in reaponse['allchannelJson']['channel_sxf_tq'] else ''
                            merchantchannel_data['channel_mchid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_no']
                            merchantchannel_data['channel_id'] = 3
                            merchantchannel_data['sub_mchid'] = reaponse['allchannelJson']['channel_sxf_tq']['merchant_other_3']
                            MerchantChannel.objects.get_or_create(defaults=merchantchannel_data, merchant_id=merchant.id, channel_id=merchantchannel_data['channel_id'])
                    if reaponse['channel_mch_id'] == '227823797':
                        try:
                            channel_url = 'https://manage.gdwxyf.com/internal/searchmerchantchannelmsg.do'
                            channel_body = {"account": "gdyouzheng", "shopId": sign}
                            channel_response = requests.post(url=channel_url, data=channel_body)
                            channel_result = channel_response.json()
                            merchantchannel_data['smid'] = channel_result['alipaychannelJson']['merchant_other_4'] if 'merchant_other_4' in channel_result['alipaychannelJson'] else ''
                            merchantchannel_data['channel_mchid'] = channel_result['alipaychannelJson']['upMerchantNo']
                            merchantchannel_data['channel_id'] = 15
                            merchantchannel_data['sub_mchid'] = channel_result['wxchannelJson']['merchant_other_3']
                            MerchantChannel.objects.get_or_create(defaults=merchantchannel_data, merchant_id=merchant.id, channel_id=merchantchannel_data['channel_id'])
                        except:
                            pass
                    transaction.savepoint_commit(save_id)
                except Exception as e:
                    logger.error(e)
                    transaction.savepoint_rollback(save_id)
                    return Response({'error': '获取商户信息错误,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)
        data = MerchantRetrieveModelSerializer(merchant).data
        return Response(data, status=status.HTTP_200_OK)

    def batch_check_merchant(self, request):
        user = self.request.iser
        # business_no = self.request.data.get('business_no', None)
        # if not business_no:
        #     return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        # business_no = str(business_no).replace(' ', '')
        # if not business_no.isdigit() and len(business_no) != 10:
        #     return Response({'error': '请输入10位纯数字微邮付商户号'}, status=status.HTTP_400_BAD_REQUEST)
        redis_conn = get_redis_connection("stock_mchid")
        is_run_server = redis_conn.get("is_run")
        if is_run_server:
            return Response({'error': "有正在执行的任务,请稍候"}, status=status.HTTP_400_BAD_REQUEST)
        redis_conn.set("is_run", 1)
        my_file = self.request.FILES.get('my_file', None)
        user = self.request.iser
        if not my_file:
            redis_conn.delete("is_run")
            return Response({'error': '请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        type_excel = my_file.name.split('.')[-1]
        file_contents = my_file.read()
        if type_excel not in ['xlsx', 'xls']:
            redis_conn.delete("is_run")
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            wb = xlrd.open_workbook(filename=None, file_contents=file_contents)  # 关键点在于这里
        except:
            redis_conn.delete("is_run")
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        table = wb.sheets()[0]
        title = table.row_values(0)
        if '批次号' not in title:
            redis_conn.delete("is_run")
            return Response({'error': '表格字段缺少"批次号"'}, status=status.HTTP_400_BAD_REQUEST)
        if '商户号' not in title:
            redis_conn.delete("is_run")
            return Response({'error': '表格字段缺少"商户号"'}, status=status.HTTP_400_BAD_REQUEST)
        business_no_index = title.index('商户号')
        stock_index = title.index('批次号')
        nrows = table.nrows
        data = {}
        cookies = Channel.objects.get(id=4)
        headers = {'Cookie': cookies.cookie}
        business_no_list = list(Merchant.objects.all().values_list('wyfMerchantID', flat=True))
        for i in range(1, nrows):
            rowValues = table.row_values(i)
            business_no = str(rowValues[business_no_index]).replace('.0', '').replace(' ', '')
            if not business_no.isdigit() and len(business_no) != 10:
                return Response({'error': '请输入10位纯数字微邮付商户号'}, status=status.HTTP_400_BAD_REQUEST)
        # merchant = Merchant.objects.filter(wyfMerchantID=business_no).first()
        # if merchant:
        #     pass
        # else:
        md5 = hashlib.md5()  # 使用MD5加密模式
        md5.update(str(business_no).encode('utf-8'))  # 将参数字符串传入
        sign = md5.hexdigest()  # 完成加密并转为大写
        url = 'https://manage.gdwxyf.com/internal/searchmerchantbyshopids.do'
        body = {
            'account': 'gdyouzheng',
            'shopId': sign
        }
        reaponse = requests.post(url=url, data=body)
        try:
            reaponse = reaponse.json()
        except:
            return Response({'error': '系统繁忙,请重新操作'}, status=status.HTTP_400_BAD_REQUEST)
        if 'wx_channel_merchant_id' not in reaponse:
            return Response({'error': '商户号有误,请检查重新操作'}, status=status.HTTP_400_BAD_REQUEST)
        if reaponse['wx_channel_merchant_id'] == '':
            return Response({'error': '获取商户信息失败，请确认该商户是否审核成功。'}, status=status.HTTP_400_BAD_REQUEST)
        with transaction.atomic():
            # 创建事务保存点
            save_id = transaction.savepoint()
            try:
                merchant_data = {}
                channel_mch_id = reaponse['channel_mch_id']
                channel = Channel.objects.filter(mchid=channel_mch_id).first()
                if channel:
                    merchant_data['channel_id'] = channel.id
                merchant_data['name'] = reaponse['shopName']
                merchant_data['short_name'] = reaponse['shopNickname']
                merchant_data['wyfMerchantID'] = business_no
                merchant_data['ruiyinxinMerchantID'] = sign
                merchant_data['create_time'] = datetime.datetime.now()
                if reaponse['businessType'] == '1':
                    merchant_data['subject_type'] = 3
                elif reaponse['businessType'] == '2':
                    merchant_data['subject_type'] = 2
                elif reaponse['businessType'] == '3':
                    merchant_data['subject_type'] = 1
                else:
                    merchant_data['subject_type'] = 4
                level = Level.objects.filter(acceptId=reaponse['accept_id']).first()
                if level:
                    merchant_data['level_id'] = level.id
                merchantuser = MerchantUser.objects.filter(mobile=reaponse['phone']).first()
                if merchantuser:
                    merchant_data['merchantuser_id'] = merchantuser.id
                merchant_data['account_name'] = reaponse['cardName']
                merchant_data['account_bank'] = reaponse['bankName']
                merchant_data['account_number'] = reaponse['card']
                card_copy = f'youhuiquanyi/{business_no}/card_copy.png'
                card_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/cardFace.png', key=card_copy)
                if card_copy_url:
                    merchant_data['card_copy'] = card_copy
                merchant_data['identification_number'] = reaponse['identity']
                merchant_data['store_address'] = reaponse['address']
                store_header_copy = f'youhuiquanyi/{business_no}/store_header_copy.png'
                store_header_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/merchantHead.png', key=store_header_copy)
                if store_header_copy_url:
                    merchant_data['store_header_copy'] = store_header_copy
                merchant, cereate = Merchant.objects.update_or_create(defaults=merchant_data, wyfMerchantID=business_no)
                if not merchant.managerName:
                    merchant.managerName = reaponse['keeper']
                if not merchant.managermobile:
                    merchant.managermobile = reaponse['phone']
                if not merchant.prov_name:
                    merchant.prov_name = reaponse['province']
                    merchant.city_name = reaponse['city']
                    merchant.district_name = reaponse['area']
                if not merchant.business_category:
                    merchant.business_category_id = 1
                if not merchant.store_logo_copy:
                    merchant.store_logo_copy = 'default_merchant_logo.jpg'
                merchant.save()
                if cereate and merchant.channel:
                    store_header_copy = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                    store_header_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/merchantHead.png', key=store_header_copy)
                    if store_header_copy_url:
                        MerchantImage.objects.create(merchant_id=merchant.id, image=store_header_copy)
                    store_indoor_copy = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                    store_indoor_copy_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/otherPhoto3.png', key=store_indoor_copy)
                    if store_indoor_copy_url:
                        MerchantImage.objects.create(merchant_id=merchant.id, image=store_indoor_copy)
                    merchantCheck = f'youhuiquanyi/{business_no}/merchant_image{"%06d" % randint(0, 999999)}.png'
                    merchantCheck_url = sync_img_to_local(f'http://ossmerchant.gdwxyf.com/{sign}/merchantCheck.png', key=merchantCheck)
                    if merchantCheck_url:
                        MerchantImage.objects.create(merchant_id=merchant.id, image=merchantCheck)
                    merchantchannel_data = {
                        'merchant_id': merchant.id
                    }
                    if merchant.channel_id != 2:
                        if reaponse['allchannelJson']['channel_ruiyinxin']:
                            merchantchannel_data['smid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_4']
                            merchantchannel_data['channel_mchid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_7']
                            merchantchannel_data['channel_id'] = 2
                            merchantchannel_data['sub_mchid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_3']
                            MerchantChannel.objects.get_or_create(defaults=merchantchannel_data, merchant_id=merchant.id, channel_id=merchant.channel_id)
                            channel_response = requests.post(
                                url='https://manage.gdwxyf.com/internal/searchmerchant.do',
                                data={'shopId': business_no, 'account': 'gdyouzheng'},
                            ).json()['showList'][0]
                            merchantchannel_data['smid'] = channel_response['allchannelJson']['channel_sxf_tq']['shop_merchant_other_4']
                            merchantchannel_data['channel_mchid'] = channel_response['allchannelJson']['channel_sxf_tq']['shop_merchant_no']
                            merchantchannel_data['channel_id'] = 3
                            merchantchannel_data['sub_mchid'] = channel_response['allchannelJson']['channel_sxf_tq']['shop_merchant_other_3']
                            MerchantChannel.objects.get_or_create(defaults=merchantchannel_data, merchant_id=merchant.id, channel_id=merchant.channel_id)
                    else:
                        merchantchannel_data['smid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_4']
                        merchantchannel_data['channel_mchid'] = reaponse['allchannelJson']['channel_ruiyinxin']['merchant_other_7']
                        merchantchannel_data['channel_id'] = merchant.channel_id
                        merchantchannel_data['sub_mchid'] = reaponse['wx_channel_merchant_id']
                        MerchantChannel.objects.get_or_create(defaults=merchantchannel_data, merchant_id=merchant.id, channel_id=merchant.channel_id)
                transaction.savepoint_commit(save_id)
            except Exception as e:
                logger.error(e)
                transaction.savepoint_rollback(save_id)
                return Response({'error': '获取商户信息错误,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)
        data = MerchantRetrieveModelSerializer(merchant).data
        return Response(data, status=status.HTTP_200_OK)

    def update_logo(self, request):
        user = self.request.iser
        img = self.request.FILES.get('img', None)
        merchant_id = self.request.data.get('merchant_id', None)
        if not all([img, merchant_id]):
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        if img.size > 2097152:
            return Response({'error': '照片不得超过2M,请重新上传!'}, status=status.HTTP_400_BAD_REQUEST)
        img_str = img.read()
        img.seek(0)
        try:
            merchant = Merchant.objects.get(id=merchant_id)
        except:
            return Response({'error': '无效商户号'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            img_id = f'youhuiquanyi/{merchant.wyfMerchantID}/store_logo_copy{"%06d" % randint(0, 999999)}.png'
            mid_img = pic_upload(img_str, key=img_id)
        except Exception as e:
            logger.error(e)
            return Response({'error': '图片上传失败,请重新上传!'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            old_store_logo_copy = merchant.store_logo_copy
            pic_delete(old_store_logo_copy)
        except:
            pass
        merchant.store_logo_copy = img_id
        merchant.save()
        merchant = Merchant.objects.get(id=merchant.id)
        return Response({'success': '操作成功', 'store_logo_copy': merchant.store_logo_copy.url}, status=status.HTTP_200_OK)

    def delete_image(self, request):
        user = self.request.iser
        img_id = self.request.data.get('img_id', None)
        instance = MerchantImage.objects.filter(id=img_id).first()
        try:
            old_store_logo_copy = instance.image
            pic_delete(old_store_logo_copy)
        except:
            pass
        instance.delete()
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)

    def add_image(self, request):
        user = self.request.iser
        img = self.request.FILES.get('img', None)
        merchant_id = self.request.data.get('merchant_id', None)
        if not all([img, merchant_id]):
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        if img.size > 2097152:
            return Response({'error': '照片不得超过2M,请重新上传!'}, status=status.HTTP_400_BAD_REQUEST)
        img_str = img.read()
        img.seek(0)
        try:
            merchant = Merchant.objects.get(id=merchant_id)
        except:
            return Response({'error': '无效商户号'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            img_id = f'youhuiquanyi/{merchant.wyfMerchantID}/merchant_image{"%06d" % randint(0, 999999)}.png'
            mid_img = pic_upload(img_str, key=img_id)
        except Exception as e:
            logger.error(e)
            return Response({'error': '图片上传失败,请重新上传!'}, status=status.HTTP_400_BAD_REQUEST)
        instance = MerchantImage.objects.create(merchant_id=merchant.id, image=img_id)
        data = MerchantImageSerializer(instance).data
        return Response({'success': '操作成功', 'img': data}, status=status.HTTP_200_OK)

    def update(self, request, *args, **kwargs):
        instance = self.get_object()
        managerName = self.request.data.get('managerName', None)
        managermobile = self.request.data.get('managermobile', None)
        short_name = self.request.data.get('short_name', None)
        merchant_desc = self.request.data.get('merchant_desc', None)
        business_category_id = self.request.data.get('business_category_id', None)
        open_work_time = self.request.data.get('open_work_time', None)
        close_work_time = self.request.data.get('close_work_time', None)
        longitude = self.request.data.get('longitude', None)
        latitude = self.request.data.get('latitude', None)
        store_address = self.request.data.get('store_address', None)
        is_online = self.request.data.get('is_online', None)
        prov_name = self.request.data.get('prov_name', None)
        city_name = self.request.data.get('city_name', None)
        district_name = self.request.data.get('district_name', None)
        prov_code = self.request.data.get('prov_code', None)
        city_code = self.request.data.get('city_code', None)
        district_code = self.request.data.get('district_code', None)
        settlement_amount_by_day = self.request.data.get('settlement_amount_by_day', None)
        if managerName:
            instance.managerName = managerName
        if settlement_amount_by_day:
            instance.settlement_amount_by_day = settlement_amount_by_day
        if managermobile:
            merchantuser, create = MerchantUser.objects.get_or_create(mobile=managermobile, defaults={"mobile": managermobile})
            instance.merchantuser_id = merchantuser.id
            instance.managermobile = managermobile
            try:
                WaivePaymentMerchant.objects.filter(merchant_id=instance.id).update(merchant_user_id=merchantuser.id)
            except:
                pass
            conn = pymysql.connect(host=settings.DATABASES['open']['HOST'],
                                   port=settings.DATABASES['open']['PORT'],
                                   database=settings.DATABASES['open']['NAME'],
                                   user=settings.DATABASES['open']['USER'],
                                   password=settings.DATABASES['open']['PASSWORD'])
            cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
            try:
                sql = f'''UPDATE tb_m_merchant_info SET mobile={managermobile} WHERE merchant_id IN (SELECT id FROM tb_m_merchant WHERE business_no={instance.wyfMerchantID});'''
                cursor.execute(sql)
                sql = f'''SELECT id FROM tb_m_merchant_user WHERE mobile={managermobile};'''
                cursor.execute(sql)
                row = cursor.fetchone()
                if row is not None:
                    merchant_user_id = row['id']
                    sql = f'''UPDATE tb_m_merchant SET merchant_user_id={merchant_user_id} WHERE business_no={instance.wyfMerchantID};'''
                    cursor.execute(sql)
                else:
                    sql = f'''UPDATE tb_m_merchant SET merchant_user_id=null WHERE business_no={instance.wyfMerchantID};'''
                    cursor.execute(sql)
                conn.commit()
            except Exception as e:
                print(e)
                conn.rollback()
            cursor.close()
            conn.close()
        if short_name:
            instance.short_name = short_name
        if merchant_desc:
            instance.merchant_desc = merchant_desc
        if business_category_id:
            try:
                business_category = MerchantBusinessCategory.objects.get(id=business_category_id)
            except:
                return Response({'error': '无效经营类目'}, status=status.HTTP_400_BAD_REQUEST)
            instance.business_category_id = business_category_id
        if open_work_time:
            try:
                time.strptime(str(open_work_time), "%H:%M")
            except:
                return Response({'error': '经营时间参数有误!'}, status=status.HTTP_400_BAD_REQUEST)
            busStartTm = f'1900-01-01 {open_work_time}:00'
            instance.open_work_time = busStartTm
        if close_work_time:
            try:
                time.strptime(str(close_work_time), "%H:%M")
            except:
                return Response({'error': '经营时间参数有误!'}, status=status.HTTP_400_BAD_REQUEST)
            busEndTm = f'1900-01-01 {close_work_time}:00'
            instance.close_work_time = busEndTm
        if longitude:
            instance.longitude = longitude
        if latitude:
            instance.latitude = latitude
        if prov_name:
            instance.prov_name = prov_name
        if city_name:
            instance.city_name = city_name
        if district_name:
            instance.district_name = district_name
        if prov_code:
            instance.prov_code = prov_code
        if city_code:
            instance.city_code = city_code
        if district_code:
            instance.district_code = district_code
        if store_address:
            instance.store_address = store_address
        if is_online:
            if str(is_online) not in ['1', '2', '3']:
                return Response({'error': '上下架参数有误'}, status=status.HTTP_400_BAD_REQUEST)
            instance.is_online = is_online
        try:
            instance.save()
            return Response({'success': '保存成功'}, status=status.HTTP_200_OK)
        except:
            return Response({'error': '保存失败,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)

    def partial_update(self, request, *args, **kwargs):
        user = self.request.iser
        instance = self.get_object()
        enter_array = self.request.data.get('enter_array', None)
        if not enter_array:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        if not isinstance(enter_array, list):
            return Response({'error': '参数格式有误'}, status=status.HTTP_400_BAD_REQUEST)
        for enter in enter_array:
            enter_data = {}
            try:
                level = Level.objects.get(id=enter['level_id'])
                if level.level != 4:
                    continue
                if str(enter['is_online']) not in ['2', '3']:
                    continue
                enter_data['level_id'] = enter['level_id']
                enter_data['managerName'] = enter['managerName']
                enter_data['managermobile'] = enter['managermobile']
                enter_data['is_online'] = enter['is_online']
                enter_data['merchant_id'] = instance.id
                enter_data['user_id'] = user.id
                enter_data['create_time'] = datetime.datetime.now()
                MerchantEnter.objects.update_or_create(defaults=enter_data, level_id=enter_data['level_id'], merchant_id=enter_data['merchant_id'])
            except:
                continue
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)

    def enter_level(self, request):
        user = self.request.iser
        level = user.level
        search = self.request.query_params.get('search', None)
        merchant_id = self.request.query_params.get('merchant_id', None)
        if not merchant_id:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            merchant = Merchant.objects.get(id=merchant_id)
        except:
            return Response({'error': '无效商户id'}, status=status.HTTP_400_BAD_REQUEST)
        if level.level == 0:
            queryset = Level.objects.filter(level=4)
        elif level.level == 1:
            queryset = Level.objects.filter(prov_id=level.id, level=4)
        elif level.level == 2:
            queryset = Level.objects.filter(city_id=level.id, level=4)
        elif level.level == 3:
            queryset = Level.objects.filter(district_id=level.id, level=4)
        else:
            queryset = Level.objects.filter(branch_id=level.id, level=4)
        if search:
            # queryset = queryset.filter(Q(name__contains=search) | Q(account__contains=search))
            try:
                search = search.replace('，', ',')
                search = search.split(',')
                if '' in search:
                    search.remove('')
                queryset = queryset.filter(account__in=search)
            except:
                pass
        page = self.paginate_queryset(queryset)
        if page is not None:
            serializer = EnterLvelModelSerializer(page, many=True, context={'merchant': merchant})
            return self.get_paginated_response(serializer.data)

        serializer = EnterLvelModelSerializer(queryset, many=True, context={'merchant': merchant})
        return Response(serializer.data)

    def export_merchant(self, request):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        create_time_begin = self.request.query_params.get('create_time_begin', None)
        create_time_end = self.request.query_params.get('create_time_end', None)
        search = self.request.query_params.get('search', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE id!=0'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if search:
            condition = condition + f' AND (name LIKE "%{search}%" OR short_name LIKE "%{search}%" OR wyfMerchantID LIKE "%{search}%")'
        if create_time_begin:
            condition = condition + f' AND create_time >= "{create_time_begin + " 00:00:00"}"'
        if create_time_end:
            condition = condition + f' AND create_time <= "{create_time_end + " 23:59:59"}"'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT c.*,d.account,city_name,district_name,branch_name FROM (SELECT * FROM (SELECT id,wyfMerchantID,managerName,name,level_id,settlement_amount_by_day FROM tb_merchant {condition})AS a LEFT JOIN (SELECT merchant_id,MAX(CASE channel_id WHEN 2 THEN sub_mchid ELSE '' END)AS ryx_sub_mchid,MAX(CASE channel_id WHEN 2 THEN smid ELSE '' END)AS ryx_smid,MAX(CASE channel_id WHEN 3 THEN sub_mchid ELSE '' END)AS sxf_sub_mchid,MAX(CASE channel_id WHEN 3 THEN smid ELSE '' END)AS sxf_smid FROM tb_merchant_channel WHERE merchant_id IN (SELECT id FROM tb_merchant {condition}) GROUP BY merchant_id)AS b ON a.id=b.merchant_id)AS c LEFT JOIN (SELECT id,account,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id;
            '''
            cursor.execute(sql)
        except Exception as e:
            sql = f'''
            SELECT c.*,d.account,city_name,district_name,branch_name FROM (SELECT * FROM (SELECT id,wyfMerchantID,managerName,name,level_id,settlement_amount_by_day FROM tb_merchant)AS a LEFT JOIN (SELECT merchant_id,MAX(CASE channel_id WHEN 2 THEN sub_mchid ELSE '' END)AS ryx_sub_mchid,MAX(CASE channel_id WHEN 2 THEN smid ELSE '' END)AS ryx_smid,MAX(CASE channel_id WHEN 3 THEN sub_mchid ELSE '' END)AS sxf_sub_mchid,MAX(CASE channel_id WHEN 3 THEN smid ELSE '' END)AS sxf_smid FROM tb_merchant_channel GROUP BY merchant_id)AS b ON a.id=b.merchant_id)AS c LEFT JOIN (SELECT id,account,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id;
            '''
            cursor.execute(sql)
        ziduan = ['id', '微邮付商户号', '商户简称', '商户全称', '瑞银信间连微信商户号', '瑞银信支付宝商户号', '随行付间连微信商户号', '随行付支付宝商户号', '网点机构号', '所属市公司', '所属区县', '所属网点', '结算限额/天']
        cols = ['id', 'wyfMerchantID', 'managerName', 'name', 'ryx_sub_mchid', 'ryx_smid', 'sxf_sub_mchid', 'sxf_smid', 'account', 'city_name', 'district_name', 'branch_name', 'settlement_amount_by_day']
        response = StreamingHttpResponse(self.get_merchant_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('商户列表.csv'))
        return response

    def get_merchant_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col in ['ryx_smid', 'sxf_smid']:
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"

    def is_add_merchant(self, request):
        redis_conn = get_redis_connection("stock_mchid")
        is_run_server = redis_conn.get("is_add_merchant")
        if is_run_server:
            return Response({'state': "1"}, status=status.HTTP_200_OK)
        else:
            return Response({'state': "0"}, status=status.HTTP_200_OK)

    def add_merchant(self, request):
        redis_conn = get_redis_connection("stock_mchid")
        is_run_server = redis_conn.get("is_add_merchant")
        if is_run_server:
            return Response({'error': "有正在执行的任务,请稍候"}, status=status.HTTP_400_BAD_REQUEST)
        business_no_array = self.request.data.get('business_no_array', None)
        if not isinstance(business_no_array, list):
            return Response({'error': '参数类型有误'}, status=status.HTTP_400_BAD_REQUEST)
        if len(business_no_array) > 1000:
            return Response({'error': '最大不能超过1000商户同时同步'}, status=status.HTTP_400_BAD_REQUEST)
        redis_conn.set("is_add_merchant", 1)
        add_merchant_array.delay({'business_no_array': business_no_array})
        return Response({'success': '提交修改成功,等待系统处理'}, status=status.HTTP_200_OK)

    def open_is_mall(self, request):
        merchant_id = self.request.query_params.get('merchant_id', None)
        if not merchant_id:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        merchant = self.get_queryset().filter(id=merchant_id).first()
        if not merchant:
            return Response({'error': '无效商户id'}, status=status.HTTP_400_BAD_REQUEST)
        if merchant.is_mall != 0:
            return Response({'error': '商户商圈状态有误'}, status=status.HTTP_400_BAD_REQUEST)
        '''校验商户当前通道'''
        try:
            url = 'https://api.gdwxyf.com/api/searchmerchantmsg.do'
            key = '6715C7D4435343BA0459EAEC2334D81B'
            body = {
                "program_id": "202316837141378884",
                "shop_id": merchant.wyfMerchantID
            }
            sign = get_sign(body, key)
            body['sign'] = sign
            response = requests.post(url=url, json=body)
            result = response.json()
            if result['pay_type'] != 'sxf_tq':
                return Response({'error': '商户通道不支持入驻商圈'}, status=status.HTTP_400_BAD_REQUEST)
        except:
            pass
        if merchant.channel_id != 3:
            return Response({'error': '商户通道不支持入驻商圈'}, status=status.HTTP_400_BAD_REQUEST)
        '''商户授权应用id'''
        url = 'https://api.gdwxyf.com/api/shopBindApp.do'
        key = '6715C7D4435343BA0459EAEC2334D81B'
        body = {
            "program_id": "202316837141378884",
            "shop_id": merchant.wyfMerchantID
        }
        sign = get_sign(body, key)
        body['sign'] = sign
        response = requests.post(url=url, json=body)
        result = response.json()
        if result['result_code'] != 'success' and result['error_msg'] != '商户与应用已绑定，请勿重复绑定。':
            return Response({'error': result['error_msg']}, status=status.HTTP_400_BAD_REQUEST)
        '''商户绑定授权appid'''
        url = 'https://api.gdwxyf.com/api/setMerchantWxAppMsg.do'
        body = {
            "program_id": "202316837141378884",
            "shop_id": merchant.ruiyinxinMerchantID,
            "config_type": 'appid',
            "appid": "wx950122ad80dc97c9"
        }
        sign = get_sign(body, key)
        body['sign'] = sign
        response = requests.post(url=url, json=body)
        result = response.json()
        if result['result_code'] != 'success' and result['error_msg'] != '微信公众号配置已配置':
            return Response({'error': result['error_msg']}, status=status.HTTP_400_BAD_REQUEST)
        merchant.is_mall = 1
        merchant.save()
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)

    def confirm_is_mall(self, request):
        user = self.request.iser
        merchant_id = self.request.data.get('merchant_id', None)
        reject_reason = self.request.data.get('reject_reason', None)
        applyment_state = self.request.data.get('applyment_state', None)
        datetime_now = datetime.datetime.now()
        if not merchant_id:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        merchant = self.get_queryset().filter(id=merchant_id).first()
        if not merchant:
            return Response({'error': '无效商户id'}, status=status.HTTP_400_BAD_REQUEST)
        if merchant.is_mall != 5:
            return Response({'error': '商户商圈状态有误'}, status=status.HTTP_400_BAD_REQUEST)
        mall_applyment = MerchantMallApplyment.objects.filter(merchant_id=merchant.id, applyment_type=1).last()
        if str(applyment_state) == '1':
            # if merchant.applyment_state == 5:
            #     store_indoor_copy = MerchantImage.objects.filter(merchant_id=merchant.id).first()
            #     if not store_indoor_copy:
            #         store_indoor_copy = merchant.store_header_copy.url
            #     else:
            #         store_indoor_copy = store_indoor_copy.image.url
            #     result = submit_sign_contract(merchant.ruiyinxinMerchantID, mall_applyment.contract.url, merchant.store_header_copy.url, store_indoor_copy)
            #     if result['data']['sign_status'] == 1:
            #         merchant.applyment_state = 4
            #         merchant.save()
            #         mall_applyment.applyment_state = 4
            #         signUrl = result['data']['sign_url']
            #         sign_url = make_qrcode1(merchant.ruiyinxinMerchantID, signUrl, key=None)
            #         mall_applyment.sign_url = sign_url
            #         mall_applyment.save()
            #         return Response({'success': '提交资料成功,等待审核'}, status=status.HTTP_200_OK)
            #     else:
            #         return Response({'error': result['data']['message']}, status=status.HTTP_400_BAD_REQUEST)
            # else:
            #     if merchant.applyment_state != 2:
            #         return Response({'error': '请等待商户签约完成再审核'}, status=status.HTTP_400_BAD_REQUEST)
            try:
                '''创建门店'''
                url = 'https://manage.gdwxyf.com/internal/savebranchstoremsg.do'
                body = {
                    "deviceName": "微邮惠商圈",
                    "shopId": merchant.wyfMerchantID,
                    "account": "gdyouzheng"
                }
                response = requests.post(url=url, data=body)
                result = response.json()
                device_id = result['device_id']
                '''绑定设备id'''
                url = 'https://manage.gdwxyf.com/internal/savenewqrcode.do'
                body = {
                    "deviceName": "微邮惠商圈",
                    "shopId": merchant.wyfMerchantID,
                    "wsyDeviceId": f'{merchant.wyfMerchantID}2024',
                    "deviceId": device_id
                }
                response = requests.post(url=url, data=body)
                result = response.json()
                if result['result_code'] == 'fail':
                    return Response({'error': result['error_msg']}, status=status.HTTP_400_BAD_REQUEST)
            except:
                return Response({'error': '创建门店失败,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)
            merchant.is_mall = 2
            merchant.device = f'{merchant.wyfMerchantID}2024'
            merchant.applyment_state = 2
            merchant.verify_state = 2
            merchant.is_profit_share = 4
            merchant.save()
            if mall_applyment:
                mall_applyment.applyment_state = 2
                mall_applyment.audit_time = datetime_now
                mall_applyment.verify_state = 2
                mall_applyment.verify_time = datetime_now
                mall_applyment.verify_user_id = user.id
                mall_applyment.save()
            return Response({'success': '审核成功'}, status=status.HTTP_200_OK)
        elif str(applyment_state) == '2':
            merchant.is_mall = 1
            merchant.applyment_state = 3
            merchant.save()
            if mall_applyment:
                mall_applyment.applyment_state = 3
                mall_applyment.audit_time = datetime_now
                mall_applyment.reject_reason = reject_reason
                mall_applyment.verify_user_id = user.id
                mall_applyment.save()
            return Response({'success': '驳回成功'}, status=status.HTTP_200_OK)
        else:
            return Response({'error': '审核参数有误'}, status=status.HTTP_400_BAD_REQUEST)

    def batch_enter_level(self, request):
        my_file = self.request.FILES.get('my_file', None)
        user = self.request.iser
        if not my_file:
            return Response({'error': '请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        type_excel = my_file.name.split('.')[-1]
        file_contents = my_file.read()
        if type_excel not in ['xlsx', 'xls']:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            wb = xlrd.open_workbook(filename=None, file_contents=file_contents)  # 关键点在于这里
        except:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        table = wb.sheets()[0]
        nrows = table.nrows
        title = table.row_values(0)
        if '商户号' not in title:
            return Response({'error': '表格字段缺少"商户号"'}, status=status.HTTP_400_BAD_REQUEST)
        if '机构号' not in title:
            return Response({'error': '表格字段缺少"机构号"'}, status=status.HTTP_400_BAD_REQUEST)
        if '负责人姓名' not in title:
            return Response({'error': '表格字段缺少"负责人姓名"'}, status=status.HTTP_400_BAD_REQUEST)
        if '负责人手机号码' not in title:
            return Response({'error': '表格字段缺少"负责人手机号码"'}, status=status.HTTP_400_BAD_REQUEST)
        file_name = f'{my_file.name.replace(type_excel, "").replace(".", "")}_{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}.{type_excel}'
        file = pic_upload(file_contents, file_name)
        check_quest = MerchantEnterQuest.objects.filter(file_status=2)
        if check_quest:
            file_status = 1
        else:
            file_status = 2
        quest = MerchantEnterQuest.objects.create(create_user_id=user.id, file=file, file_status=file_status, total_line=nrows-1)
        if file_status == 2:
            operation_merchant_enter_quest.delay({'id': quest.id})
            # operation_merchant_bank_quest({'id': quest.id})
        return Response({'success': '上传文件成功'}, status=status.HTTP_200_OK)

    def update_merchant_channel(self, request):
        merchant_id = self.request.data.get('merchant_id', None)
        if not merchant_id:
            return Response({'error': '请上传商户id'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            merchant = self.get_queryset().get(id=merchant_id)
        except:
            return Response({'error': '无效商户id'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            url = 'https://api.gdwxyf.com/api/searchmerchantmsg.do'
            key = '6715C7D4435343BA0459EAEC2334D81B'
            body = {
                "program_id": "202316837141378884",
                "shop_id": merchant.wyfMerchantID
            }
            sign = get_sign(body, key)
            body['sign'] = sign
            response = requests.post(url=url, json=body)
            result = response.json()
            if result['pay_type'] == 'sxf_tq':
                merchant.channel_id = 3
            elif result['pay_type'] == 'ruiyinxin':
                merchant.channel_id = 2
            elif result['pay_type'] == 'guotong':
                merchant.channel_id = 15
            merchant.save()
        except:
            pass
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)


class MerchantMonthlyBillModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    filter_class = MerchantMonthlyBillFilter
    search_fields = ('merchant__name', 'merchant__wyfMerchantID')

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        receipts_time_begin = self.request.query_params.get('receipts_time_begin', None)
        receipts_time_end = self.request.query_params.get('receipts_time_end', None)
        level_id = self.request.query_params.get('level', None)
        try:
            if '_mutable' in self.request.query_params.__dict__:
                self.request.query_params.__dict__['_mutable'] = True
        except:
            pass
        if receipts_time_begin:
            try:
                datetime.datetime.strptime(receipts_time_begin, '%Y-%m')
            except:
                raise serializers.ValidationError({'error': '时间格式有误'})
            self.request.query_params['receipts_time_begin'] = f'{receipts_time_begin}-01'
        if receipts_time_end:
            try:
                datetime.datetime.strptime(receipts_time_end, '%Y-%m')
            except:
                raise serializers.ValidationError({'error': '时间格式有误'})
            self.request.query_params['receipts_time_end'] = f'{receipts_time_end}-01'
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        queryset = MerchantMonthlyBill.objects.all()
        if level.level == 0:
            pass
        elif level.level == 1:
            queryset = queryset.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = queryset.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = queryset.filter(level__district_id=level.id)
        else:
            queryset = queryset.filter(level__branch_id=level.id)
        return queryset.order_by('-receipts_time', '-id')

    def get_serializer_class(self):
        if self.action == 'list':
            return MerchantMonthlyBillListModelSerializer
        else:
            return MerchantMonthlyBillRetrieveModelSerializer


class MerchantEightEightActivityModelViewSet(ModelViewSet):

    queryset = MerchantEightEightActivity.objects.all().order_by('-activity_end_time')
    serializer_class = MerchantEightEightActivityModelSerializer
    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('activity_name', )
    filter_class = MerchantEightEightActivityFilter

    def add_eighteightactivity_white_table(self, request):
        activity_id = self.request.data.get('activity_id', None)
        business_no_array = self.request.data.get('business_no_array', None)
        try:
            instance = self.get_queryset().get(id=activity_id)
        except:
            return Response({'error': '无效活动id'}, status=status.HTTP_400_BAD_REQUEST)
        if instance.activity_status == 3:
            return Response({'error': '活动已结束'}, status=status.HTTP_400_BAD_REQUEST)
        for business_no in business_no_array:
            defaults = {}
            defaults['activity_id'] = activity_id
            defaults['business_no'] = business_no
            defaults['is_win_type'] = 2
            MerchantEightEightWhiteTable.objects.get_or_create(activity_id=activity_id, business_no=business_no, defaults=defaults)
        return Response({'success': '修改成功'}, status=status.HTTP_200_OK)

    def partial_update(self, request, *args, **kwargs):
        instance = self.get_object()
        user = self.request.iser
        is_keguan = self.request.data.get('is_keguan', '')
        pushEventIds = self.request.data.get('pushEventIds', '')
        if str(is_keguan) == '1':
            if not pushEventIds:
                return Response({'error': '请上传营销事件ID'}, status=status.HTTP_400_BAD_REQUEST)
            if not instance.level.account_nine:
                return Response({'error': '活动所属机构尚未绑定9位机构号,请联系管理员处理'}, status=status.HTTP_400_BAD_REQUEST)
            keguan_data = {}
            keguan_data['sourceId'] = f'SHDB{str(instance.id).zfill(7)}'
            keguan_data['pushEventIds'] = str(pushEventIds).split('|')
            keguan_data['orgNo'] = instance.level.account_nine
            keguan_data['activityName'] = instance.activity_name
            keguan_data['startTime'] = instance.activity_start_time.strftime("%Y-%m-%dT%H:%M:%S.120+08:00")
            keguan_data['endTime'] = instance.activity_end_time.strftime("%Y-%m-%dT%H:%M:%S.120+08:00")
            keguan_result = ylx_api('ThirdActInfoSyncGdCrm', keguan_data)
            if keguan_result['meta']['code'] != 200:
                return Response({'error': f"同步客管系统失败,原因:{keguan_result['meta']['msg']}"}, status=status.HTTP_400_BAD_REQUEST)
            MerchantEightEightActivityKeguanRecords.objects.create(activity_id=instance.id, operate_user_id=user.id, operate_type=1)
            instance.is_keguan = 1
            instance.pushEventIds = pushEventIds
            instance.save()
            return Response({'success': '操作成功'}, status=status.HTTP_200_OK)
        elif str(is_keguan) == '0':
            MerchantEightEightActivityKeguanRecords.objects.create(activity_id=instance.id, operate_user_id=user.id, operate_type=2)
            instance.is_keguan = 0
            instance.save()
            return Response({'success': '操作成功'}, status=status.HTTP_200_OK)
        else:
            return Response({'error': '参数有误'}, status=status.HTTP_400_BAD_REQUEST)



class MerchantEightEightWhiteTableModelViewSet(ModelViewSet):

    # serializer_class = MerchantEightEightWhiteTableModelSerializer
    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('business_no', )

    def get_queryset(self):
        activity_id = self.request.query_params.get('activity_id', None)
        if activity_id:
            activity = MerchantEightEightActivity.objects.filter(id=activity_id).first()
            if activity.activity_type == 3 and activity.id != 47:
                queryset = MerchantEightEightActivityOrder.objects.filter(activity_id=activity_id)
            else:
                queryset = MerchantEightEightWhiteTable.objects.filter(activity_id=activity_id)
        else:
            queryset = MerchantEightEightWhiteTable.objects.filter(activity_id=activity_id)
        return queryset

    def get_serializer_class(self):
        activity_id = self.request.query_params.get('activity_id', None)
        if activity_id:
            activity = MerchantEightEightActivity.objects.filter(id=activity_id).first()
            if activity.activity_type == 3 and activity.id != 47:
                return MerchantEightEightActivityOrderModelSerializer
            else:
                return MerchantEightEightWhiteTableModelSerializer
        else:
            return MerchantEightEightWhiteTableModelSerializer

    def export_merchantreachwhitetable(self, request):
        activity_id = self.request.query_params.get('activity_id', None)
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        if activity_id:
            activity = MerchantEightEightActivity.objects.filter(id=activity_id).first()
            if activity.activity_type == 3 and activity.id != 47:
                sql = f'SELECT * FROM (SELECT * FROM (SELECT business_no,activity_id,is_standard,trade_amount,DATE_FORMAT(trade_time,"%Y-%m-%d")AS trade_time FROM tb_merchant_eight_eight_activity_order WHERE activity_id={activity_id})AS a LEFT JOIN (SELECT name,wyfMerchantID,subject_type,level_id FROM tb_merchant)AS b ON a.business_no=b.wyfMerchantID)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id;'
                ziduan = ['商户号', '商户名称', '商户类型', '所属市公司', '所属区县', '所属网点', '交易日期', '交易金额', '是否达标']
                cols = ['business_no', 'name', 'subject_type', 'city_name', 'district_name', 'branch_name', 'trade_time', 'trade_amount', 'is_standard']
            else:
                sql = f'SELECT * FROM (SELECT * FROM (SELECT business_no,activity_id FROM tb_merchant_eigth_eight_white_table WHERE activity_id={activity_id})AS a LEFT JOIN (SELECT name,wyfMerchantID,subject_type,level_id FROM tb_merchant)AS b ON a.business_no=b.wyfMerchantID)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id;'
                ziduan = ['商户号', '商户名称', '商户类型', '所属市公司', '所属区县', '所属网点']
                cols = ['business_no', 'name', 'subject_type', 'city_name', 'district_name', 'branch_name']
        else:
            sql = 'SELECT * FROM (SELECT * FROM (SELECT business_no,activity_id FROM tb_merchant_eigth_eight_white_table)AS a LEFT JOIN (SELECT name,wyfMerchantID,subject_type,level_id FROM tb_merchant)AS b ON a.business_no=b.wyfMerchantID)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id;'
            ziduan = ['商户号', '商户名称', '商户类型', '所属市公司', '所属区县', '所属网点']
            cols = ['business_no', 'name', 'subject_type', 'city_name', 'district_name', 'branch_name']
        try:
            cursor.execute(sql)
        except:
            sql = 'SELECT * FROM (SELECT * FROM (SELECT business_no,activity_id FROM tb_merchant_eigth_eight_white_table WHERE activity_id=0)AS a LEFT JOIN (SELECT name,wyfMerchantID,subject_type,level_id FROM tb_merchant)AS b ON a.business_no=b.wyfMerchantID)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id;'
            cursor.execute(sql)
        # ziduan = ['商户号', '商户名称', '商户类型', '所属市公司', '所属区县', '所属网点']
        # cols = ['business_no', 'name', 'subject_type', 'city_name', 'district_name', 'branch_name']
        response = StreamingHttpResponse(self.get_merchantreachwhitetable_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('交易达标商户明细.csv'))
        return response

    def get_merchantreachwhitetable_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        SUBJECT_TYPE_ENUM = {
            1: '小微',
            2: '个体',
            3: '企业',
            4: '其他',
            5: '事业单位'
        }
        IS_STANDARDS_ENUM = {
            0: '否',
            1: '是',
            2: '是'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'subject_type':
                    tmp_str += '"%s",' % (SUBJECT_TYPE_ENUM[row[col]])
                elif col == 'is_standard':
                    tmp_str += '"%s",' % (IS_STANDARDS_ENUM[row[col]])
                elif col == 'trade_amount':
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '0')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class MerchantEightEightActivityOrderModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('business_no', )

    def get_queryset(self):
        activity_id = self.request.query_params.get('activity_id', None)
        queryset = MerchantEightEightWhiteTable.objects.filter()
        if activity_id:
            queryset = queryset.filter(activity_id=activity_id)
        return queryset

    def get_serializer_class(self):
        return MerchantEightEightWhiteTableModelSerializer

    def export_merchantreachordertable(self, request):
        activity_id = self.request.query_params.get('activity_id', None)
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        sql = f'SELECT * FROM (SELECT * FROM (SELECT business_no,activity_id FROM tb_merchant_eigth_eight_white_table WHERE activity_id={activity_id})AS a LEFT JOIN (SELECT name,wyfMerchantID,subject_type,level_id FROM tb_merchant)AS b ON a.business_no=b.wyfMerchantID)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id;'
        ziduan = ['商户号', '商户名称', '商户类型', '所属市公司', '所属区县', '所属网点']
        cols = ['business_no', 'name', 'subject_type', 'city_name', 'district_name', 'branch_name']
        try:
            cursor.execute(sql)
        except:
            sql = 'SELECT * FROM (SELECT * FROM (SELECT business_no,activity_id FROM tb_merchant_eigth_eight_white_table WHERE activity_id=0)AS a LEFT JOIN (SELECT name,wyfMerchantID,subject_type,level_id FROM tb_merchant)AS b ON a.business_no=b.wyfMerchantID)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id;'
            cursor.execute(sql)
        # ziduan = ['商户号', '商户名称', '商户类型', '所属市公司', '所属区县', '所属网点']
        # cols = ['business_no', 'name', 'subject_type', 'city_name', 'district_name', 'branch_name']
        response = StreamingHttpResponse(self.get_merchantreachordertable_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('交易达标商户明细.csv'))
        return response

    def get_merchantreachordertable_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        SUBJECT_TYPE_ENUM = {
            1: '小微',
            2: '个体',
            3: '企业',
            4: '其他',
            5: '事业单位'
        }
        IS_STANDARDS_ENUM = {
            0: '否',
            1: '是',
            2: '是'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'subject_type':
                    tmp_str += '"%s",' % (SUBJECT_TYPE_ENUM[row[col]])
                elif col == 'is_standard':
                    tmp_str += '"%s",' % (IS_STANDARDS_ENUM[row[col]])
                elif col == 'trade_amount':
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '0')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class MerchantEightEightActivityPrizeModelViewSet(ModelViewSet):

    serializer_class = MerchantEightEightActivityPrizeModelSerializer
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    filter_class = MerchantEightEightActivityPrizeFilter
    queryset = MerchantEightEightActivityPrize.objects.all()

    def update(self, request, *args, **kwargs):
        instance = self.get_object()
        count = self.request.data.get('count', None)
        second_count = self.request.data.get('second_count', None)
        instance.count = count
        instance.second_count = second_count
        instance.save()
        return Response({'success': '修改成功'}, status=status.HTTP_200_OK)


class MerchantBusinessQRCodeVisitModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    # search_fields = ('merchant__wyfMerchantID', 'sn')
    filter_class = MerchantBusinessQRCodeVisitFilter

    def get_queryset(self):
        queryset = MerchantBusinessQRCodeVisit.objects.all().order_by('-create_time')
        return queryset

    def get_serializer_class(self):
        return MerchantBusinessQRCodeVisitListModelSerializer


class MerchantBusinessQRCodeModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('merchant__wyfMerchantID', 'sn')
    filter_class = MerchantBusinessQRCodeFilter

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = MerchantBusinessQRCode.objects.all()
        elif level.level == 1:
            queryset = MerchantBusinessQRCode.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = MerchantBusinessQRCode.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = MerchantBusinessQRCode.objects.filter(level__district_id=level.id)
        else:
            queryset = MerchantBusinessQRCode.objects.filter(level__branch_id=level.id)
        return queryset

    def get_serializer_class(self):
        if self.action == 'list':
            return MerchantBusinessQRCodeListModelSerializer
        else:
            return MerchantBusinessQRCodeRetrieveModelSerializer

    def export_merchantbusinessqrcode(self, request):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        bind_time_begin = self.request.query_params.get('bind_time_begin', None)
        bind_time_end = self.request.query_params.get('bind_time_end', None)
        is_bind = self.request.query_params.get('is_bind', None)
        is_c_bind = self.request.query_params.get('is_c_bind', None)
        search = self.request.query_params.get('search', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE id!=0'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if search:
            condition = condition + f' AND (sn LIKE "%{search}%" OR merchant_id IN (SELECT id FROM tb_merchant WHERE wyfMerchantID LIKE "%{search}%"))'
        if bind_time_begin:
            condition = condition + f' AND bind_time >= "{bind_time_begin + " 00:00:00"}"'
        if bind_time_end:
            condition = condition + f' AND bind_time <= "{bind_time_end + " 23:59:59"}"'
        if is_bind:
            condition = condition + f' AND is_bind IN ({is_bind})'
        if is_c_bind:
            condition = condition + f' AND is_c_bind IN ({is_c_bind})'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT k.*,l.mobile FROM (SELECT i.*,j.state,received_time,amount FROM (SELECT g.*,h.visit_count FROM (SELECT e.*,f.nickname FROM (SELECT c.*,d.city_name,district_name,branch_name FROM (SELECT a.*,b.name,short_name,wyfMerchantID FROM (SELECT id,sn,merchant_id,DATE_FORMAT(bind_time,'%Y-%m-%d %H:%i:%S') AS bind_time,is_bind,customeruservice_id,is_c_bind,DATE_FORMAT(c_bind_time,'%Y-%m-%d %H:%i:%S') AS c_bind_time,level_id,user_id,inviter_count,inviter_total_count,DATE_FORMAT(award_time,'%Y-%m-%d %H:%i:%S') AS award_time,is_achieve,cashactivitydata_id FROM tb_merchant_business_qrcode {condition})AS a LEFT JOIN (SELECT id,name,short_name,wyfMerchantID FROM tb_merchant)AS b ON a.merchant_id=b.id)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id)AS e LEFT JOIN (SELECT id,nickname FROM tb_u_user)AS f ON e.user_id=f.id)AS g LEFT JOIN (SELECT qrcode_id,COUNT(qrcode_id) AS visit_count FROM tb_merchant_business_qrcode_visit GROUP BY qrcode_id)AS h ON g.id=h.qrcode_id)AS i LEFT JOIN (SELECT id,state,received_time,amount FROM tb_cash_activity_data)AS j ON i.cashactivitydata_id=j.id)AS k LEFT JOIN (SELECT id,mobile FROM tb_u_customer_user_vice)AS l ON k.customeruservice_id=l.id;
            '''
            cursor.execute(sql)
        except Exception as e:
            sql = f'''
            SELECT k.*,l.mobile FROM (SELECT i.*,j.state,received_time,amount FROM (SELECT g.*,h.visit_count FROM (SELECT e.*,f.nickname FROM (SELECT c.*,d.city_name,district_name,branch_name FROM (SELECT a.*,b.name,short_name,wyfMerchantID FROM (SELECT id,sn,merchant_id,DATE_FORMAT(bind_time,'%Y-%m-%d %H:%i:%S') AS bind_time,is_bind,customeruservice_id,is_c_bind,DATE_FORMAT(c_bind_time,'%Y-%m-%d %H:%i:%S') AS c_bind_time,level_id,user_id,inviter_count,inviter_total_count,DATE_FORMAT(award_time,'%Y-%m-%d %H:%i:%S') AS award_time,is_achieve,cashactivitydata_id FROM tb_merchant_business_qrcode)AS a LEFT JOIN (SELECT id,name,short_name,wyfMerchantID FROM tb_merchant)AS b ON a.merchant_id=b.id)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id)AS e LEFT JOIN (SELECT id,nickname FROM tb_u_user)AS f ON e.user_id=f.id)AS g LEFT JOIN (SELECT qrcode_id,COUNT(qrcode_id) AS visit_count FROM tb_merchant_business_qrcode_visit GROUP BY qrcode_id)AS h ON g.id=h.qrcode_id)AS i LEFT JOIN (SELECT id,state,received_time,amount FROM tb_cash_activity_data)AS j ON i.cashactivitydata_id=j.id)AS k LEFT JOIN (SELECT id,mobile FROM tb_u_customer_user_vice)AS l ON k.customeruservice_id=l.id;
            '''
            cursor.execute(sql)
        ziduan = ['id', '商圈编码', '码牌绑定状态', '绑定时间', '微邮付商户号', '商户全称', '商户简称', '市公司', '区县', '网点', '客户经理姓名', '店主认证状态', '认证时间', '店主手机号', '扫码人数', '邀请数', '邀请成功数', '邀约任务完成状态', '客户经理领取奖励状态', '领奖时间', '领取奖励金额']
        cols = ['id', 'sn', 'is_bind', 'bind_time', 'wyfMerchantID', 'name', 'short_name', 'city_name', 'district_name', 'branch_name', 'nickname', 'is_c_bind', 'c_bind_time', 'mobile', 'visit_count', 'inviter_total_count', 'inviter_count', 'is_achieve', 'state', 'received_time', 'amount']
        response = StreamingHttpResponse(self.get_merchantbusinessqrcode_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('商圈码绑定状态.csv'))
        return response

    def get_merchantbusinessqrcode_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        IS_BIND_ENUM = {
            0: '未绑定',
            1: '已绑定'
        }
        IS_C_BIND_ENUM = {
            0: '未认证',
            1: '已认证'
        }
        IS_ACHIEVE_ENUM = {
            0: '未完成',
            1: '已完成'
        }
        STATE_ENUM = {
            1: '待领取',
            2: '已领取',
            3: '领取失败',
            4: '已撤销',
            5: '领取中'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'is_bind':
                    tmp_str += '"%s",' % (IS_BIND_ENUM[row[col]])
                elif col == 'is_c_bind':
                    tmp_str += '"%s",' % (IS_C_BIND_ENUM[row[col]])
                elif col == 'is_achieve':
                    tmp_str += '"%s",' % (IS_ACHIEVE_ENUM[row[col]])
                elif col == 'state':
                    try:
                        tmp_str += '"%s",' % (STATE_ENUM[row[col]])
                    except:
                        tmp_str += '"%s",' % ('待领取')
                elif col in ['inviter_total_count', 'inviter_count', 'visit_count', 'amount']:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '0')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class MerchantEightEightDrawModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('business_no', 'merchantuser__mobile')
    filter_class = MerchantEightEightDrawFilter

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = MerchantEightEightDraw.objects.all()
        elif level.level == 1:
            queryset = MerchantEightEightDraw.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = MerchantEightEightDraw.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = MerchantEightEightDraw.objects.filter(level__district_id=level.id)
        else:
            queryset = MerchantEightEightDraw.objects.filter(level__branch_id=level.id)
        return queryset.filter(id__gt=16).order_by('-id')

    def get_serializer_class(self):
        if self.action == 'list':
            return MerchantEightEightDrawListModelSerializer
        else:
            return MerchantEightEightDrawRetrieveModelSerializer

    def export_merchanteightdraw(self, request):
        user = self.request.iser
        level_id = self.request.query_params.get('level_id', None)
        create_time_begin = self.request.query_params.get('create_time_begin', None)
        create_time_end = self.request.query_params.get('create_time_end', None)
        amount_min = self.request.query_params.get('amount_min', None)
        amount_max = self.request.query_params.get('amount_max', None)
        state = self.request.query_params.get('state', None)
        activity_id = self.request.query_params.get('activity_id', None)
        search = self.request.query_params.get('search', None)
        level = user.level
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE win_type!=6 AND id>16'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id}) AND win_type!=6 AND id>16'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id}) AND win_type!=6 AND id>16'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id}) AND win_type!=6 AND id>16'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id}) AND win_type!=6 AND id>16'
        if search:
            condition = condition + f' AND (merchantuser_id IN (SELECT id FROM tb_m_merchant_user WHERE mobile LIKE "%{search}%") OR business_no LIKE "%{search}%")'
        if state:
            condition = condition + f' AND state in ({state})'
        if create_time_begin:
            condition = condition + f' AND create_time >= "{create_time_begin + " 00:00:00"}"'
        if create_time_end:
            condition = condition + f' AND create_time <= "{create_time_end + " 23:59:59"}"'
        if amount_min:
            condition = condition + f' AND amount >= {amount_min}'
        if amount_max:
            condition = condition + f' AND amount <= {amount_max}'
        if activity_id:
            condition = condition + f' AND activity_id = {activity_id}'

        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT i.*,j.channel_name FROM (SELECT g.*,h.activity_name FROM (SELECT * FROM (SELECT c.*,d.city_name,district_name,branch_name FROM (SELECT a.*,b.mobile FROM (SELECT id,business_no,amount,payment_no,state,merchantuser_id,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S')AS create_time,DATE_FORMAT(received_time,'%Y-%m-%d %H:%i:%S')AS received_time,login_id,realname,return_msg,level_id,activity_id,channel_id FROM tb_merchant_eigth_eight_draw {condition})AS a LEFT JOIN (SELECT id,mobile FROM tb_m_merchant_user)AS b ON a.merchantuser_id=b.id)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id)AS e LEFT JOIN (SELECT wyfMerchantID,short_name FROM tb_merchant)AS f ON e.business_no=f.wyfMerchantID)AS g LEFT JOIN (SELECT activity_id,activity_name FROM tb_merchant_eight_eight_activity)AS h ON g.activity_id=h.activity_id)AS i LEFT JOIN (SELECT id,name AS channel_name FROM tb_channel)AS j ON i.channel_id=j.id;
            '''
            cursor.execute(sql)
        except:
            sql = f'''
            SELECT i.*,j.channel_name FROM (SELECT g.*,h.activity_name FROM (SELECT * FROM (SELECT c.*,d.city_name,district_name,branch_name FROM (SELECT a.*,b.mobile FROM (SELECT id,business_no,amount,payment_no,state,merchantuser_id,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S')AS create_time,DATE_FORMAT(received_time,'%Y-%m-%d %H:%i:%S')AS received_time,login_id,realname,return_msg,level_id,activity_id,channel_id FROM tb_merchant_eigth_eight_draw)AS a LEFT JOIN (SELECT id,mobile FROM tb_m_merchant_user)AS b ON a.merchantuser_id=b.id)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id)AS e LEFT JOIN (SELECT wyfMerchantID,short_name FROM tb_merchant)AS f ON e.business_no=f.wyfMerchantID)AS g LEFT JOIN (SELECT activity_id,activity_name FROM tb_merchant_eight_eight_activity)AS h ON g.activity_id=h.activity_id)AS i LEFT JOIN (SELECT id,name AS channel_name FROM tb_channel)AS j ON i.channel_id=j.id;
            '''
            cursor.execute(sql)
        ziduan = ['ID', '活动名称', '商户号', '商户简称', '付款单号', '状态', '中奖金额', '抽奖时间', '领奖时间', '领奖账号', '实名姓名', '领奖人手机号', '市公司', '区公司', '网点', '派发平台', '领取失败原因']
        cols = ['id', 'activity_name', 'business_no', 'short_name', 'payment_no', 'state', 'amount', 'create_time', 'received_time', 'login_id', 'realname', 'mobile', 'city_name', 'district_name', 'branch_name', 'channel_name', 'return_msg']
        response = StreamingHttpResponse(self.get_merchanteightdraw_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('88节达标商户中奖明细.csv'))
        return response

    def get_merchanteightdraw_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        STATE_ENUM = {
            1: '待领取',
            2: '已领取',
            3: '领取失败',
            5: '领取中'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'state':
                    tmp_str += '"%s",' % (STATE_ENUM[row[col]])
                elif col == 'return_msg':
                    if row['state'] == 3:
                        tmp_str += '"%s",' % (row[col])
                elif col in ['payment_no']:
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"

#商户达标奖.客管推送
class MerchantEightEightDrawKeguanModelViewSet(ModelViewSet):
    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    filter_class = MerchantEightEightDrawKeguanFilter
    search_fields = ('draw__payment_no',)

    def get_queryset(self):
        queryset = MerchantEightEightDrawKeguan.objects.all()
        return queryset.order_by('-id')

    def get_serializer_class(self):
        return MerchantEightEightDrawKeGuanListModelSerializer

class MerchantBillModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('merchant__name', 'merchant__wyfMerchantID')
    filter_class = MerchantBillFilter

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = MerchantBill.objects.all()
        elif level.level == 1:
            queryset = MerchantBill.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = MerchantBill.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = MerchantBill.objects.filter(level__district_id=level.id)
        else:
            queryset = MerchantBill.objects.filter(level__branch_id=level.id)
        return queryset.order_by('-receipts_time', 'id')

    def get_serializer_class(self):
        if self.action == 'list':
            return MerchantBillListModelSerializer
        else:
            return MerchantBillRetrieveModelSerializer

    def export_merchant_bill(self, request):
        user = self.request.iser
        receipts_time_end = self.request.query_params.get('receipts_time_end', None)
        receipts_time_begin = self.request.query_params.get('receipts_time_begin', None)
        settlement_time_end = self.request.query_params.get('settlement_time_end', None)
        settlement_time_begin = self.request.query_params.get('settlement_time_begin', None)
        payable_amount_min = self.request.query_params.get('payable_amount_min', None)
        payable_amount_max = self.request.query_params.get('payable_amount_max', None)
        level_id = self.request.query_params.get('level', None)
        bill_state = self.request.query_params.get('bill_state', None)
        search = self.request.query_params.get('search', None)
        bill_type = self.request.query_params.get('bill_type', None)
        channel_id = self.request.query_params.get('channel_id', None)
        level = user.level
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE id!=0'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if search:
            condition = condition + f' AND (merchant_id IN (SELECT id FROM tb_merchant WHERE name LIKE "%{search}%" OR short_name LIKE "%{search}%" OR wyfMerchantID LIKE "%{search}%"))'
        if bill_state:
            condition = condition + f' AND bill_state in ({bill_state})'
        if bill_type:
            condition = condition + f' AND bill_type in ({bill_type})'
        if channel_id:
            condition = condition + f' AND channel_id in ({channel_id})'
        if receipts_time_begin:
            condition = condition + f' AND receipts_time >= "{receipts_time_begin + " 00:00:00"}"'
        if receipts_time_end:
            condition = condition + f' AND receipts_time <= "{receipts_time_end + " 23:59:59"}"'
        if settlement_time_begin:
            condition = condition + f' AND settlement_time >= "{settlement_time_begin + " 00:00:00"}"'
        if settlement_time_end:
            condition = condition + f' AND settlement_time <= "{settlement_time_end + " 23:59:59"}"'
        if payable_amount_min:
            condition = condition + f' AND payable_amount >= "{payable_amount_min}"'
        if payable_amount_max:
            condition = condition + f' AND payable_amount <= "{payable_amount_max}"'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT e.*,f.channel_name FROM (SELECT c.*,d.city_name,district_name,branch_name FROM (SELECT a.*,b.name,short_name,wyfMerchantID FROM (SELECT id,merchant_id,DATE_FORMAT(receipts_time,'%Y-%m-%d')AS receipts_time,DATE_FORMAT(settlement_time,'%Y-%m-%d %H:%i:%S')AS settlement_time,payable_amount,bill_state,account_bank,account_name,account_number,settlement_amount,level_id,channel_id,other_amount,other_commission,other_settlement_amount FROM tb_merchant_bill {condition})AS a LEFT JOIN(SELECT id,name,short_name,wyfMerchantID FROM tb_merchant)AS b ON a.merchant_id=b.id)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id)AS e LEFT JOIN (SELECT id,name AS channel_name FROM tb_channel)AS f ON e.channel_id=f.id;
            '''
            cursor.execute(sql)
        except:
            sql = f'''
            SELECT e.*,f.channel_name FROM (SELECT c.*,d.city_name,district_name,branch_name FROM (SELECT a.*,b.name,short_name,wyfMerchantID FROM (SELECT id,merchant_id,DATE_FORMAT(receipts_time,'%Y-%m-%d')AS receipts_time,DATE_FORMAT(settlement_time,'%Y-%m-%d %H:%i:%S')AS settlement_time,payable_amount,bill_state,account_bank,account_name,account_number,settlement_amount,level_id,channel_id,other_amount,other_commission,other_settlement_amount FROM tb_merchant_bill)AS a LEFT JOIN(SELECT id,name,short_name,wyfMerchantID FROM tb_merchant)AS b ON a.merchant_id=b.id)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id)AS e LEFT JOIN (SELECT id,name AS channel_name FROM tb_channel)AS f ON e.channel_id=f.id;
            '''
            cursor.execute(sql)
        ziduan = ['结算ID', '商户全称', '商户简称', '微邮付商户号', '清分日期', '结算时间', '清分金额', '结算金额',
                  '结算状态', '归属市公司', '归属区县', '归属网点', '结算通道', '结算户名', '结算卡号', '微邮付交易金额',
                  '微邮付交易手续费', '微邮付结算金额']
        cols = ['id', 'name', 'short_name', 'wyfMerchantID', 'receipts_time', 'settlement_time', 'payable_amount', 'settlement_amount',
                'bill_state', 'city_name', 'district_name', 'branch_name', 'channel_name', 'account_name', 'account_number', 'other_amount',
                'other_commission', 'other_settlement_amount']
        response = StreamingHttpResponse(self.get_merchant_bill_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('结算单信息.csv'))
        return response

    def get_merchant_bill_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        STATUS_ENUM = {
            1: '待结算',
            2: '结算中',
            3: '已结算',
            4: '已撤销',
            5: '结算失败'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'bill_state':
                    tmp_str += '"%s",' % (STATUS_ENUM[row[col]])
                elif col in ['other_commission', 'other_settlement_amount', 'other_amount']:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] != None else '-')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"

    def update(self, request, *args, **kwargs):
        user = self.request.iser
        instance = self.get_object()
        if instance.bill_state != 1:
            return Response({'error': '结算流水已绑定,不可重复操作'}, status=status.HTTP_400_BAD_REQUEST)
        record_array = self.request.data.get('record_array', None)
        if not record_array:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            merchantsettlementrecord_set = MerchantSettlementRecord.objects.filter(is_bind=0, merchant_id=instance.merchant_id,
                                                           id__in=record_array,
                                                           settlement_time__gt=instance.receipts_time)
        except:
            return Response({'error': '无效清分记录参数'}, status=status.HTTP_400_BAD_REQUEST)
        if not merchantsettlementrecord_set:
            return Response({'error': '无效清分记录参数'}, status=status.HTTP_400_BAD_REQUEST)
        total_amount = merchantsettlementrecord_set.aggregate(Sum('amount'))['amount__sum']
        # if user.id != 1:
        if instance.bill_type == 2:
            if (instance.payable_amount + instance.other_settlement_amount) != total_amount:
                return Response({'error': '清分金额与结算金额不符'}, status=status.HTTP_400_BAD_REQUEST)
        else:
            if instance.payable_amount != total_amount:
                return Response({'error': '清分金额与结算金额不符'}, status=status.HTTP_400_BAD_REQUEST)
        for merchantsettlementrecord in merchantsettlementrecord_set:
            relations_data = {}
            relations_data['settlement_record_id'] = merchantsettlementrecord.id
            relations_data['bill_id'] = instance.id
            MerchantBillSettlementRecord.objects.get_or_create(defaults=relations_data,
                                                               settlement_record_id=merchantsettlementrecord.id,
                                                               bill_id=instance.id)
            merchantsettlementrecord.is_bind = 1
            merchantsettlementrecord.save()
        merchantsettlementrecord = merchantsettlementrecord_set[0]
        instance.account_name = merchantsettlementrecord.account_name
        instance.account_bank = merchantsettlementrecord.account_bank
        instance.account_number = merchantsettlementrecord.account_number
        instance.settlement_time = merchantsettlementrecord.settlement_time
        instance.settlement_record_id = merchantsettlementrecord.id
        instance.bill_state = merchantsettlementrecord.status
        instance.channel_id = merchantsettlementrecord.channel_id
        instance.settlement_amount = total_amount
        instance.save()
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)

    def synch_trading(self, request):
        # begin_time = self.request.data.get('begin_time', None)
        # end_time = self.request.data.get('end_time', None)
        # if not all([begin_time, end_time]):
        #     return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        # try:
        #     time_min1 = int(time.mktime(time.strptime(str(begin_time) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
        #     time_max1 = int(time.mktime(time.strptime(str(end_time) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
        # except:
        #     return Response({'error': '时间区间参数有误!'}, status=status.HTTP_400_BAD_REQUEST)
        # if time_max1 - time_min1 > 2678400:
        #     return Response({'error': '时区不得大于31天!'}, status=status.HTTP_400_BAD_REQUEST)
        # time_min1 = begin_time + ' 00:00:00'
        # time_max1 = end_time + ' 23:59:59'
        # sync_weiyoufu_trading({'begin_time': time_min1, 'end_time': time_max1})
        sync_weiyoufu_trading.delay()
        return Response({'success': '已提交后台处理,请稍后刷新看看'}, status=status.HTTP_200_OK)


class MerchantBusinessCategoryModelViewSet(ModelViewSet):

    queryset = MerchantBusinessCategory.objects.all()
    serializer_class = MerchantBusinessCategoryModelSerializer


class WaivePaymentActivityModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = WaivePaymentActivityFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('activity_name', )
    queryset = WaivePaymentActivity.objects.all().order_by('-id')
    serializer_class = WaivePaymentActivityModelSerializer

    def create(self, request, *args, **kwargs):
        user = self.request.iser
        activity_name = self.request.data.get('activity_name', None)
        available_begin_time = self.request.data.get('available_begin_time', None)
        available_end_time = self.request.data.get('available_end_time', None)
        desc = self.request.data.get('desc', None)
        description = self.request.data.get('description', None)
        is_repeat = self.request.data.get('is_repeat', None)
        if not all([activity_name, available_begin_time, available_end_time, desc, description]):
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            datetime.datetime.strptime(available_begin_time, '%Y-%m-%d %H:%M:%S')
            datetime.datetime.strptime(available_end_time, '%Y-%m-%d %H:%M:%S')
        except:
            return Response({'error': '活动时间参数格式有误'}, status=status.HTTP_400_BAD_REQUEST)
        if str(is_repeat) not in ['0', '1']:
            return Response({'error': '无效重复校验参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            instance = WaivePaymentActivity.objects.create(
                activity_name=activity_name,
                available_end_time=available_end_time,
                available_begin_time=available_begin_time,
                desc=desc,
                description=description,
                is_repeat=is_repeat
            )
        except:
            return Response({'error': '创建失败'}, status=status.HTTP_400_BAD_REQUEST)
        WaivePaymentAdvance.objects.create(
            waivepaymentactivity_id=instance.id,
            activity_state=1,
            remark='创建活动',
            create_user_id=user.id
        )
        return Response({'success': '创建成功', 'id': instance.id}, status=status.HTTP_200_OK)

    def update(self, request, *args, **kwargs):
        user = self.request.iser
        instance = self.get_object()
        my_file = self.request.FILES.get('my_file', None)
        trade_time_begin = self.request.data.get('trade_time_begin', None)
        trade_time_end = self.request.data.get('trade_time_end', None)
        name = self.request.data.get('name', None)
        if not all([trade_time_begin, trade_time_end]):
            return Response({'error': '请上传交易时间区间'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            trade_time_begin_stamp = int(time.mktime(time.strptime(trade_time_begin, '%Y-%m-%d %H:%M:%S')))
            trade_time_end_stamp = int(time.mktime(time.strptime(trade_time_end, '%Y-%m-%d %H:%M:%S')))
        except:
            return Response({'error': '交易时间参数格式有误'}, status=status.HTTP_400_BAD_REQUEST)
        if (trade_time_end_stamp - trade_time_begin_stamp) > 1209600:
            return Response({'error': '交易时间区间不得大于14天'}, status=status.HTTP_400_BAD_REQUEST)
        if not my_file:
            return Response({'error': '请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        type_excel = my_file.name.split('.')[-1]
        file_contents = my_file.read()
        if type_excel not in ['xlsx', 'xls']:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            wb = xlrd.open_workbook(filename=None, file_contents=file_contents)  # 关键点在于这里
        except:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        table = wb.sheets()[0]
        title = table.row_values(0)
        nrows = table.nrows - 1
        if '商户号' not in title:
            return Response({'error': '表格字段缺少"商户号"'}, status=status.HTTP_400_BAD_REQUEST)
        if '是否中奖' not in title:
            return Response({'error': '表格字段缺少"是否中奖"'}, status=status.HTTP_400_BAD_REQUEST)
        if '期数' not in title:
            return Response({'error': '表格字段缺少"期数"'}, status=status.HTTP_400_BAD_REQUEST)
        if str(table.row_values(1)[title.index('期数')]) not in ['一', '二', '三', '四', '五', '六']:
            return Response({'error': '期数参数有误'}, status=status.HTTP_400_BAD_REQUEST)
        file_name = f'{my_file.name.replace(type_excel, "").replace(".", "")}_{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}.{type_excel}'
        file = pic_upload(file_contents, file_name)
        waivepaymentadvance = WaivePaymentAdvance.objects.create(
            waivepaymentactivity_id=instance.id,
            activity_state=instance.activity_state,
            remark='上传活动商户',
            create_user_id=user.id,
            file=file,
            trade_time_begin=trade_time_begin,
            trade_time_end=trade_time_end,
            operate_type=2,
            name=name,
            join_number=nrows
        )
        instance.join_number += nrows
        instance.save()
        pull_waive_payment_merchant.delay({'id': waivepaymentadvance.id})
        # update_stock_usable_merchant({'id': history.id})
        return Response({'success': '上传文件成功'}, status=status.HTTP_200_OK)

    def partial_update(self, request, *args, **kwargs):
        user = self.request.iser
        instance = self.get_object()
        is_keguan = self.request.data.get('is_keguan', '')
        pushEventIds = self.request.data.get('pushEventIds', '')
        if str(is_keguan) == '1':
            if not pushEventIds:
                return Response({'error': '请上传营销事件ID'}, status=status.HTTP_400_BAD_REQUEST)
            keguan_data = {}
            keguan_data['sourceId'] = f'YYMD{str(instance.id).zfill(7)}'
            keguan_data['pushEventIds'] = str(pushEventIds).split('|')
            keguan_data['orgNo'] = '440000888'
            keguan_data['activityName'] = instance.activity_name
            keguan_data['startTime'] = instance.available_begin_time.strftime("%Y-%m-%dT%H:%M:%S.120+08:00")
            keguan_data['endTime'] = instance.available_end_time.strftime("%Y-%m-%dT%H:%M:%S.120+08:00")
            keguan_result = ylx_api('ThirdActInfoSyncGdCrm', keguan_data)
            if keguan_result['meta']['code'] != 200:
                return Response({'error': f"同步客管系统失败,原因:{keguan_result['meta']['msg']}"}, status=status.HTTP_400_BAD_REQUEST)
            WaivePaymentActivityKeguanRecords.objects.create(activity_id=instance.id, operate_user_id=user.id, operate_type=1)
            instance.is_keguan = 1
            instance.pushEventIds = pushEventIds
            instance.save()
            return Response({'success': '操作成功'}, status=status.HTTP_200_OK)
        elif str(is_keguan) == '0':
            WaivePaymentActivityKeguanRecords.objects.create(activity_id=instance.id, operate_user_id=user.id, operate_type=2)
            instance.is_keguan = 0
            instance.save()
            return Response({'success': '操作成功'}, status=status.HTTP_200_OK)
        if instance.activity_state == 4:
            return Response({'error': '已结束活动无法操作'}, status=status.HTTP_400_BAD_REQUEST)
        if instance.activity_state == 2:
            instance.activity_state = 3
            instance.save()
            WaivePaymentAdvance.objects.create(
                waivepaymentactivity_id=instance.id,
                activity_state=3,
                remark='暂停活动',
                create_user_id=user.id
            )
        elif instance.activity_state in [1, 3]:
            instance.activity_state = 2
            instance.save()
            WaivePaymentAdvance.objects.create(
                waivepaymentactivity_id=instance.id,
                activity_state=2,
                remark='开始活动',
                create_user_id=user.id
            )
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)

#月月免单.客管推送
class WaivePaymentActivityDataKeguanModelViewSet(ModelViewSet):
    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    filter_class = WaivePaymentMerchantKeguanFilter
    search_fields = ('payment__payment_no',)

    def get_queryset(self):
        queryset = WaivePaymentMerchantKeguan.objects.all()
        return queryset.order_by('-id')

    def get_serializer_class(self):
        return WaiVePaymentKeGuanListModelSerializer



class WaivePaymentAdvanceModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = WaivePaymentAdvanceFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('name', )
    queryset = WaivePaymentAdvance.objects.all().order_by('-id')
    serializer_class = WaivePaymentAdvanceModelSerializer

    def update(self, request, *args, **kwargs):
        user = self.request.iser
        instance = self.get_object()
        if instance.payment_state == 1:
            return Response({'error': '子活动状态有误'}, status=status.HTTP_400_BAD_REQUEST)
        if instance.operate_type == 1:
            return Response({'error': '子活动类型有误'}, status=status.HTTP_400_BAD_REQUEST)
        instance.activate_user_id = user.id
        instance.activate_time = datetime.datetime.now()
        instance.payment_state = 1
        instance.save()
        instance.waivepaymentmerchant_set.filter(payment_state=0).update(payment_state=5)
        # WaivePaymentAdvance.objects.create(
        #     waivepaymentactivity_id=instance.waivepaymentactivity_id,
        #     activity_state=2,
        #     remark=f'激活商户,文件:{instance.id},{instance.name}',
        #     create_user_id=user.id
        # )
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)


class WaivePaymentMerchantModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = WaivePaymentMerchantFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('waivepaymentactivity__activity_name', 'business_no')
    queryset = WaivePaymentMerchant.objects.all().order_by('-id')

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = WaivePaymentMerchant.objects.all()
        elif level.level == 1:
            queryset = WaivePaymentMerchant.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = WaivePaymentMerchant.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = WaivePaymentMerchant.objects.filter(level__district_id=level.id)
        else:
            queryset = WaivePaymentMerchant.objects.filter(level__branch_id=level.id)
        return queryset.order_by('-id')

    def get_serializer_class(self):
        if self.action == 'list':
            return WaivePaymentMerchantListModelSerializer
        else:
            return WaivePaymentMerchantRetrieveModelSerializer

    def export_waivepaymentmerchant(self, request):
        user = self.request.iser
        waivepaymentactivity_id = self.request.query_params.get('waivepaymentactivity_id', None)
        waivepaymentadvance_id = self.request.query_params.get('waivepaymentadvance_id', None)
        level_id = self.request.query_params.get('level', None)
        payment_state = self.request.query_params.get('payment_state', None)
        search = self.request.query_params.get('search', None)
        level = user.level
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE level_id IN (SELECT id FROM tb_u_level)'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if search:
            condition = condition + f' AND (merchant_id IN (SELECT id FROM tb_merchant WHERE short_name LIKE "%{search}%" OR wyfMerchantID LIKE "%{search}%"))'
        if payment_state:
            condition = condition + f' AND payment_state in ({payment_state})'
        if waivepaymentadvance_id:
            condition = condition + f' AND waivepaymentadvance_id={waivepaymentadvance_id}'
        if waivepaymentactivity_id:
            condition = condition + f' AND waivepaymentactivity_id={waivepaymentactivity_id}'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT i.*,j.mobile FROM (SELECT g.*,h.city_name,district_name,branch_name FROM (SELECT e.*,f.waivepaymentmerchant_id,trade_money,trade_type,trade_time,trade_no FROM (SELECT c.*,d.merchant_name,short_name FROM (SELECT a.*,b.activity_name FROM (SELECT id,waivepaymentactivity_id,business_no,merchant_id,merchant_user_id,account,name,payment_no,amount,payment_state,DATE_FORMAT(payment_time,'%Y-%m-%d %H:%i:%S')AS payment_time,company_mobile,return_msg,level_id,periods FROM tb_waive_payment_merchant {condition})AS a LEFT JOIN (SELECT id,activity_name FROM tb_waive_payment_activity)AS b ON a.waivepaymentactivity_id=b.id)AS c LEFT JOIN (SELECT id,name AS merchant_name,short_name FROM tb_merchant)AS d ON c.merchant_id=d.id)AS e LEFT JOIN (SELECT id,waivepaymentmerchant_id,trade_money,trade_type,DATE_FORMAT(trade_time_end,'%Y-%m-%d %H:%i:%S')AS trade_time,trade_no FROM tb_waive_payment_order WHERE is_hit=1)AS f ON e.id=f.waivepaymentmerchant_id)AS g LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS h ON g.level_id=h.id)AS i LEFT JOIN (SELECT id,mobile FROM tb_m_merchant_user)AS j ON i.merchant_user_id=j.id;
            '''
            cursor.execute(sql)
        except:
            sql = f'''
            SELECT i.*,j.mobile FROM (SELECT g.*,h.city_name,district_name,branch_name FROM (SELECT e.*,f.waivepaymentmerchant_id,trade_money,trade_type,trade_time,trade_no FROM (SELECT c.*,d.merchant_name,short_name FROM (SELECT a.*,b.activity_name FROM (SELECT id,waivepaymentactivity_id,business_no,merchant_id,merchant_user_id,account,name,payment_no,amount,payment_state,DATE_FORMAT(payment_time,'%Y-%m-%d %H:%i:%S')AS payment_time,company_mobile,return_msg,level_id,periods FROM tb_waive_payment_merchant)AS a LEFT JOIN (SELECT id,activity_name FROM tb_waive_payment_activity)AS b ON a.waivepaymentactivity_id=b.id)AS c LEFT JOIN (SELECT id,name AS merchant_name,short_name FROM tb_merchant)AS d ON c.merchant_id=d.id)AS e LEFT JOIN (SELECT id,waivepaymentmerchant_id,trade_money,trade_type,DATE_FORMAT(trade_time_end,'%Y-%m-%d %H:%i:%S')AS trade_time,trade_no FROM tb_waive_payment_order WHERE is_hit=1)AS f ON e.id=f.waivepaymentmerchant_id)AS g LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS h ON g.level_id=h.id)AS i LEFT JOIN (SELECT id,mobile FROM tb_m_merchant_user)AS j ON i.merchant_user_id=j.id;
            '''
            cursor.execute(sql)
        ziduan = ['ID', '活动名称', '商户号', '商户全称', '商户简称', '归属市公司', '归属区县', '归属网点',
                  '领取手机号', '领取支付宝账户', '支付宝实名信息', '打款支付宝订单号', '免单订单号', '交易金额', '交易类型', '交易时间', '期数', '是否中奖', '免单金额',
                  '领取状态', '领取时间', '参与码', '领取失败原因']
        cols = ['id', 'activity_name', 'business_no', 'merchant_name', 'short_name', 'city_name', 'district_name', 'branch_name',
                'mobile', 'account', 'name', 'payment_no', 'trade_no', 'trade_money', 'trade_type', 'trade_time', 'periods', 'is_draw', 'amount', 'payment_state',
                'payment_time', 'company_mobile', 'return_msg']
        response = StreamingHttpResponse(self.get_waivepaymentmerchant_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('月月免单领取明细.csv'))
        return response

    def get_waivepaymentmerchant_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        TRADE_TYPE_ENUM = {
            1: '支付宝',
            2: '微信',
            3: '云闪付',
            4: '邮储手机银行通道'
        }
        PAYMENT_STATE_ENUM = {
            0: '未激活',
            1: '待领取',
            2: '已领取',
            3: '领取失败',
            4: '已撤销',
            5: '待抽奖',
            6: '无效',
            7: '谢谢参与'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'trade_type':
                    try:
                        tmp_str += '"%s",' % (TRADE_TYPE_ENUM[row[col]])
                    except:
                        tmp_str += '"%s",' % ('')
                elif col == 'is_draw':
                    try:
                        if row['amount']:
                            tmp_str += '"%s",' % ('是')
                        else:
                            tmp_str += '"%s",' % ('否')
                    except:
                        tmp_str += '"%s",' % ('')
                elif col == 'payment_state':
                    tmp_str += '"%s",' % (PAYMENT_STATE_ENUM[row[col]])
                # elif col == 'amount':
                #     if row['payment_state'] in [1, 2, 3]:
                #         tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                #     else:
                #         tmp_str += '"%s",' % ''
                elif col in ['payment_no', 'trade_no']:
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class WaivePaymentOrderModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = WaivePaymentOrderFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('waivepaymentactivity__activity_name', 'business_no')
    queryset = WaivePaymentOrder.objects.all().order_by('-is_hit')
    serializer_class = WaivePaymentOrderModelSerializer


    def export_waivepaymentorder(self, request):
        user = self.request.iser
        waivepaymentactivity_id = self.request.query_params.get('waivepaymentactivity_id', None)
        waivepaymentmerchant_id = self.request.query_params.get('waivepaymentmerchant_id', None)
        # level_id = self.request.query_params.get('level', None)
        # payment_state = self.request.query_params.get('payment_state', None)
        # search = self.request.query_params.get('search', None)
        # level = user.level
        # if level_id:
        #     try:
        #         level_list = get_level_list(level)
        #         level_id = int(level_id)
        #         if level_id in level_list:
        #             level = Level.objects.filter(id=level_id).first()
        #     except:
        #         pass
        # if level.level == 0:
        #     condition = 'WHERE level_id IN (SELECT id FROM tb_u_level) AND payment_state!=6'
        # elif level.level == 1:
        #     condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id}) AND payment_state!=6'
        # elif level.level == 2:
        #     condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id}) AND payment_state!=6'
        # elif level.level == 3:
        #     condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id}) AND payment_state!=6'
        # else:
        #     condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id}) AND payment_state!=6'
        # if search:
        #     condition = condition + f' AND (merchant_id IN (SELECT id FROM tb_merchant WHERE short_name LIKE "%{search}%" OR wyfMerchantID LIKE "%{search}%"))'
        # if payment_state:
        #     condition = condition + f' AND payment_state in ({payment_state})'
        if waivepaymentmerchant_id and waivepaymentactivity_id:
            condition = f'WHERE waivepaymentmerchant_id={waivepaymentmerchant_id} AND waivepaymentactivity_id={waivepaymentactivity_id}'
        elif waivepaymentactivity_id:
            condition = f'WHERE waivepaymentactivity_id={waivepaymentactivity_id}'
        elif waivepaymentmerchant_id:
            condition = f'WHERE waivepaymentmerchant_id={waivepaymentmerchant_id}'
        else:
            condition = ''
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT c.*,d.business_no FROM (SELECT a.*,b.activity_name FROM (SELECT id,order_no,trade_no,trade_type,trade_money,DATE_FORMAT(trade_time_begin,'%Y-%m-%d %H:%i:%S')AS payment_time,channel,waivepaymentactivity_id,waivepaymentmerchant_id,is_hit FROM tb_waive_payment_order {condition})AS a LEFT JOIN (SELECT id,activity_name FROM tb_waive_payment_activity)AS b ON a.waivepaymentactivity_id=b.id)AS c LEFT JOIN (SELECT id,business_no FROM tb_waive_payment_merchant)AS d ON c.waivepaymentmerchant_id=d.id;
            '''
            cursor.execute(sql)
        except:
            sql = f'''
            SELECT c.*,d.business_no FROM (SELECT a.*,b.activity_name FROM (SELECT id,order_no,trade_no,trade_type,trade_money,DATE_FORMAT(trade_time_begin,'%Y-%m-%d %H:%i:%S')AS payment_time,channel,waivepaymentactivity_id,waivepaymentmerchant_id,is_hit FROM tb_waive_payment_order)AS a LEFT JOIN (SELECT id,activity_name FROM tb_waive_payment_activity)AS b ON a.waivepaymentactivity_id=b.id)AS c LEFT JOIN (SELECT id,business_no FROM tb_waive_payment_merchant)AS d ON c.waivepaymentmerchant_id=d.id;
            '''
            cursor.execute(sql)
        ziduan = ['ID', '活动名称', '商户号', '订单号', '流水号', '交易类型', '交易金额', '交易时间', '交易通道', '是否命中']
        cols = ['id', 'activity_name', 'business_no', 'order_no', 'trade_no', 'trade_type', 'trade_money', 'payment_time', 'channel', 'is_hit']
        response = StreamingHttpResponse(self.get_waivepaymentorder_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('月月免单领取明细.csv'))
        return response

    def get_waivepaymentorder_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        TRADE_TYPE_ENUM = {
            1: '支付宝',
            2: '微信',
            3: '云闪付'
        }
        IS_HIT_ENUM = {
            0: '未命中',
            1: '已命中'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'trade_type':
                    tmp_str += '"%s",' % (TRADE_TYPE_ENUM[row[col]])
                elif col == 'is_hit':
                    tmp_str += '"%s",' % (IS_HIT_ENUM[row[col]])
                # elif col == 'amount':
                #     if row['payment_state'] in [1, 2, 3]:
                #         tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                #     else:
                #         tmp_str += '"%s",' % ''
                elif col in ['order_no', 'trade_no']:
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class MerchantSettlementRecordModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = MerchantSettlementRecordFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('merchant__name', 'merchant__short_name', 'merchant__wyfMerchantID')

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = MerchantSettlementRecord.objects.all()
        elif level.level == 1:
            queryset = MerchantSettlementRecord.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = MerchantSettlementRecord.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = MerchantSettlementRecord.objects.filter(level__district_id=level.id)
        else:
            queryset = MerchantSettlementRecord.objects.filter(level__branch_id=level.id)
        return queryset.order_by('-settlement_time', 'id')

    def get_serializer_class(self):
        if self.action == 'list':
            return MerchantSettlementRecordListModelSerializer
        else:
            return MerchantSettlementRecordRetrieveModelSerializer

    def export_settlementrecord(self, request):
        user = self.request.iser
        settlement_time_begin = self.request.query_params.get('settlement_time_begin', None)
        settlement_time_end = self.request.query_params.get('settlement_time_end', None)
        level_id = self.request.query_params.get('level', None)
        status = self.request.query_params.get('status', None)
        channel_id = self.request.query_params.get('channel_id', None)
        search = self.request.query_params.get('search', None)
        level = user.level
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE level_id IN (SELECT id FROM tb_u_level)'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if search:
            condition = condition + f' AND (merchant_id IN (SELECT id FROM tb_merchant WHERE name LIKE "%{search}%" OR short_name LIKE "%{search}%" OR wyfMerchantID LIKE "%{search}%"))'
        if status:
            condition = condition + f' AND status in ({status})'
        if channel_id:
            condition = condition + f' AND channel_id in ({channel_id})'
        if settlement_time_begin:
            condition = condition + f' AND settlement_time >= "{settlement_time_begin + " 00:00:00"}"'
        if settlement_time_end:
            condition = condition + f' AND settlement_time <= "{settlement_time_end + " 23:59:59"}"'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT * FROM (SELECT e.*,f.channel_name FROM (SELECT c.*,d.city_name,district_name,branch_name FROM (SELECT a.*,b.merchant_name,short_name,wyfMerchantID FROM (SELECT id,account_name,account_number,status,amount,DATE_FORMAT(settlement_time,'%Y-%m-%d %H:%i:%S')AS settlement_time,fail_reason,merchant_id,channel_id,level_id FROM tb_merchant_settlement_record {condition})AS a LEFT JOIN (SELECT id,name AS merchant_name,short_name,wyfMerchantID FROM tb_merchant)AS b ON a.merchant_id=b.id)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id)AS e LEFT JOIN (SELECT id,name AS channel_name FROM tb_channel)AS f ON e.channel_id=f.id)AS j LEFT JOIN (SELECT settlement_record_id,GROUP_CONCAT(payable_amount)AS payable_amount,GROUP_CONCAT(DATE_FORMAT(receipts_time,'%Y-%m-%d'))AS receipts_time FROM (SELECT g.bill_id,settlement_record_id,h.payable_amount,receipts_time FROM (SELECT bill_id,settlement_record_id FROM tb_merchant_bill_settlement_record)AS g LEFT JOIN (SELECT id,receipts_time,payable_amount FROM tb_merchant_bill)AS h ON g.bill_id=h.id)AS i GROUP BY settlement_record_id)AS k ON j.id=k.settlement_record_id;
            '''
            cursor.execute(sql)
        except:
            sql = f'''
            SELECT * FROM (SELECT e.*,f.channel_name FROM (SELECT c.*,d.city_name,district_name,branch_name FROM (SELECT a.*,b.merchant_name,short_name,wyfMerchantID FROM (SELECT id,account_name,account_number,status,amount,DATE_FORMAT(settlement_time,'%Y-%m-%d %H:%i:%S')AS settlement_time,fail_reason,merchant_id,channel_id,level_id FROM tb_merchant_settlement_record)AS a LEFT JOIN (SELECT id,name AS merchant_name,short_name,wyfMerchantID FROM tb_merchant)AS b ON a.merchant_id=b.id)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id)AS e LEFT JOIN (SELECT id,name AS channel_name FROM tb_channel)AS f ON e.channel_id=f.id)AS j LEFT JOIN (SELECT settlement_record_id,GROUP_CONCAT(payable_amount)AS payable_amount,GROUP_CONCAT(DATE_FORMAT(receipts_time,'%Y-%m-%d'))AS receipts_time FROM (SELECT g.bill_id,settlement_record_id,h.payable_amount,receipts_time FROM (SELECT bill_id,settlement_record_id FROM tb_merchant_bill_settlement_record)AS g LEFT JOIN (SELECT id,receipts_time,payable_amount FROM tb_merchant_bill)AS h ON g.bill_id=h.id)AS i GROUP BY settlement_record_id)AS k ON j.id=k.settlement_record_id;
            '''
            cursor.execute(sql)
        ziduan = ['结算ID', '商户全称', '商户简称', '微邮付商户号', '清分日期', '结算时间', '清分金额', '结算金额',
                  '结算状态', '结算失败原因', '归属市公司', '归属区县', '归属网点', '结算通道', '结算户名', '结算卡号']
        cols = ['id', 'merchant_name', 'short_name', 'wyfMerchantID', 'receipts_time', 'settlement_time', 'payable_amount', 'amount',
                'status', 'fail_reason', 'city_name', 'district_name', 'branch_name', 'channel_name', 'account_name', 'account_number']
        response = StreamingHttpResponse(self.get_settlementrecord_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('结算单信息.csv'))
        return response

    def get_settlementrecord_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        STATUS_ENUM = {
            1: '待结算',
            2: '结算中',
            3: '已结算',
            4: '已撤销',
            5: '结算失败'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'status':
                    tmp_str += '"%s",' % (STATUS_ENUM[row[col]])
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"

    def synchronization_settlementrecord(self, request):
        user = self.request.iser
        start = self.request.data.get('start', None)
        end = self.request.data.get('end', None)
        merchant_id = self.request.data.get('merchant_id', None)
        if not all([start, end]):
            return Response({'error': '请上传交易区间'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            start1 = datetime.datetime.strptime(start, '%Y-%m-%d')
            end1 = datetime.datetime.strptime(end, '%Y-%m-%d')
        except:
            return Response({'error': '交易区间参数格式有误'}, status=status.HTTP_400_BAD_REQUEST)
        if start1 > end1:
            return Response({'error': '交易区间参数格式有误'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            merchant = Merchant.objects.get(id=merchant_id)
        except:
            return Response({'error': '错误商户号'}, status=status.HTTP_400_BAD_REQUEST)
        synchronization_settlementrecord.delay({'merchant_id': merchant_id, 'start': start, 'end': end, 'user_id': user.id})
        # synchronization_settlementrecord({'merchant_id': merchant_id, 'start': start, 'end': end})
        return Response({'success': '系统同步交易中'}, status=status.HTTP_200_OK)

    def update(self, request, *args, **kwargs):
        instance = self.get_object()
        if instance.is_bind == 1:
            return Response({'error': '结算流水已绑定,不可重复操作'}, status=status.HTTP_400_BAD_REQUEST)
        bill_array = self.request.data.get('bill_array', None)
        if not bill_array:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            merchantbill_set = MerchantBill.objects.filter(bill_state__in=[1, 5], merchant_id=instance.merchant_id, id__in=bill_array, receipts_time__lte=instance.settlement_time)
        except:
            return Response({'error': '无效清分记录参数'}, status=status.HTTP_400_BAD_REQUEST)
        if not merchantbill_set:
            return Response({'error': '无效清分记录参数'}, status=status.HTTP_400_BAD_REQUEST)
        total_amount = merchantbill_set.aggregate(Sum('payable_amount'))['payable_amount__sum']
        if instance.amount != total_amount:
            return Response({'error': '清分金额与结算金额不符'}, status=status.HTTP_400_BAD_REQUEST)
        for merchantbill in merchantbill_set:
            relations_data = {}
            relations_data['settlement_record_id'] = instance.id
            relations_data['bill_id'] = merchantbill.id
            MerchantBillSettlementRecord.objects.get_or_create(defaults=relations_data, settlement_record_id=instance.id, bill_id=merchantbill.id)
        merchantbill_set.update(
            account_name=instance.account_name,
            account_bank=instance.account_bank,
            account_number=instance.account_number,
            settlement_time=instance.settlement_time,
            settlement_record_id=instance.id,
            bill_state=instance.status,
            settlement_amount=instance.amount,
            channel_id=instance.channel_id
        )
        instance.is_bind = 1
        instance.save()
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)

    def partial_update(self, request, *args, **kwargs):
        instance = self.get_object()
        if instance.is_bind == 0:
            return Response({'error': '结算流水未绑定,无须解绑'}, status=status.HTTP_400_BAD_REQUEST)
        MerchantBill.objects.filter(merchantbillsettlementrecord__settlement_record_id=instance.id).update(
            bill_state=1,
            settlement_record_id=None
        )
        merchantbillsettlementrecord_set = MerchantBillSettlementRecord.objects.filter(settlement_record_id=instance.id).delete()
        instance.is_bind = 0
        instance.save()
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)

    def destroy(self, request, *args, **kwargs):
        instance = self.get_object()
        if instance.is_bind == 1:
            return Response({'error': '已绑定的结算流水,不可删除'}, status=status.HTTP_400_BAD_REQUEST)

    def statistical_settlement(self, request):
        time_min = self.request.query_params.get('time_min', None)
        time_max = self.request.query_params.get('time_max', None)
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        if not all([time_min, time_max]):
            return Response({'error': '缺少时间区间参数!'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            time_min1 = int(time.mktime(time.strptime(str(time_min) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
            time_max1 = int(time.mktime(time.strptime(str(time_max) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
        except:
            return Response({'error': '时间区间参数有误!'}, status=status.HTTP_400_BAD_REQUEST)
        if time_max1 - time_min1 > 2678400:
            return Response({'error': '时区不得大于31天!'}, status=status.HTTP_400_BAD_REQUEST)
        time_min1 = time_min + ' 00:00:00'
        time_max1 = time_max + ' 23:59:59'
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = ''
        elif level.level == 1:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        sxf_amount_data = {}
        ryx_amount_data = {}
        total_amount_data = {}
        sxf_data = []
        ryx_data = []
        total_data = []
        data = {}
        max_amount = 0
        sxf_amount_sum = 0
        ryx_amount_sum = 0
        total_amount_sum = 0
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        xAxis = []
        time_min = datetime.datetime.strptime(time_min, "%Y-%m-%d").date()
        time_max = datetime.datetime.strptime(time_max, "%Y-%m-%d").date()
        create_begin_time = time_min
        days_num = (time_max-time_min).days + 1
        begin_day = time_min
        sql = f'''SELECT DATE_FORMAT(settlement_time,'%Y-%m-%d')AS time,SUM(CASE channel_id WHEN 3 THEN amount ELSE 0 END)AS sxf_amount,SUM(CASE channel_id WHEN 2 THEN amount ELSE 0 END)AS ryx_amount,SUM(amount)AS total_amount FROM tb_merchant_settlement_record WHERE settlement_time>="{time_min1}" AND settlement_time<="{time_max1}" AND status=3 {condition} GROUP BY time;'''
        cursor.execute(sql)
        result = cursor.fetchall()
        while True:
            if begin_day > time_max:
                break
            xAxis.append(datetime.datetime.strftime(begin_day, '%m/%d'))
            begin_day += datetime.timedelta(days=1)
        if result:
            while True:
                if datetime.datetime.strptime(result[0]['time'], "%Y-%m-%d").date() <= create_begin_time:
                    break
                sxf_data.append(0)
                ryx_data.append(0)
                total_data.append(0)
                create_begin_time += datetime.timedelta(days=1)
            for x in result:
                while True:
                    if datetime.datetime.strptime(x['time'], "%Y-%m-%d").date() < create_begin_time:
                        break
                    if datetime.datetime.strptime(x['time'], "%Y-%m-%d").date() == create_begin_time:
                        sxf_data.append(x['sxf_amount'])
                        ryx_data.append(x['ryx_amount'])
                        total_data.append(x['total_amount'])
                        sxf_amount_sum += x['sxf_amount']
                        ryx_amount_sum += x['ryx_amount']
                        total_amount_sum += x['total_amount']
                        if max_amount < x['total_amount']:
                            max_amount = x['total_amount']
                    else:
                        sxf_data.append(0)
                        ryx_data.append(0)
                        total_data.append(0)
                    create_begin_time += datetime.timedelta(days=1)
            while True:
                if len(sxf_data) >= days_num:
                    break
                sxf_data.append(0)
                ryx_data.append(0)
                total_data.append(0)
        else:
            for i in range(days_num):
                sxf_data.append(0)
                ryx_data.append(0)
                total_data.append(0)
        sxf_amount_data['name'] = '随行付结算金额'
        sxf_amount_data['data'] = sxf_data
        ryx_amount_data['name'] = '瑞银信结算金额'
        ryx_amount_data['data'] = ryx_data
        total_amount_data['name'] = '总结算金额'
        total_amount_data['data'] = total_data
        cursor.close()
        conn.close()
        if max_amount:
            str_max_amount = str(max_amount)
            if '.' in str_max_amount:
                len_max_amount = len(str_max_amount[:str_max_amount.find('.')])
            else:
                len_max_amount = len(str_max_amount)
            to_max_amount = '1'
            while True:
                if len(to_max_amount) >= len_max_amount -1:
                    to_max_amount = int(to_max_amount)
                    break
                to_max_amount += '0'
            max_amount = math.ceil(max_amount/to_max_amount)*to_max_amount
        data['sxf_amount_data'] = sxf_amount_data
        data['ryx_amount_data'] = ryx_amount_data
        data['total_amount_data'] = total_amount_data
        data['max_amount'] = max_amount
        data['xAxis'] = xAxis
        data['ryx_amount_sum'] = ryx_amount_sum
        data['sxf_amount_sum'] = sxf_amount_sum
        data['total_amount_sum'] = total_amount_sum
        return Response(data, status=status.HTTP_200_OK)

    def export_statistical_settlement(self, request):
        time_min = self.request.query_params.get('time_min', None)
        time_max = self.request.query_params.get('time_max', None)
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        if not all([time_min, time_max]):
            return Response({'error': '缺少时间区间参数!'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            time_min1 = int(time.mktime(time.strptime(str(time_min) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
            time_max1 = int(time.mktime(time.strptime(str(time_max) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
        except:
            return Response({'error': '时间区间参数有误!'}, status=status.HTTP_400_BAD_REQUEST)
        time_min1 = time_min + ' 00:00:00'
        time_max1 = time_max + ' 23:59:59'
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = ''
        elif level.level == 1:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        sql = f'''
        SELECT DATE_FORMAT(settlement_time,'%Y-%m-%d')AS time,SUM(CASE channel_id WHEN 3 THEN amount ELSE 0 END)AS sxf_amount,SUM(CASE channel_id WHEN 2 THEN amount ELSE 0 END)AS ryx_amount,SUM(amount)AS total_amount FROM tb_merchant_settlement_record WHERE settlement_time>="{time_min1}" AND settlement_time<="{time_max1}" AND status=3 {condition} GROUP BY time;
        '''
        cursor.execute(sql)
        ziduan = ['日期', '机构名称', '总结算金额', '瑞银信结算金额', '随行付结算金额']
        cols = ['time', 'name', 'total_amount', 'ryx_amount', 'sxf_amount']
        response = StreamingHttpResponse(self.get_statistical_settlement_fromat(cols, cursor, conn, ziduan, level.name))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('结算统计.csv'))
        return response

    def get_statistical_settlement_fromat(self, cols, cursor, conn, ziduan, level_name):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'name':
                    tmp_str += '"%s",' % (level_name)
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else 0)
            yield tmp_str.strip(',') + "\n"


class MerchantBankQRCodeQuestModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = MerchantBankQRCodeQuestFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    # search_fields = ('user__nickname', )
    serializer_class = MerchantBankQRCodeQuestModelSerializer
    queryset = MerchantBankQRCodeQuest.objects.all().order_by('-id')

    def create(self, request, *args, **kwargs):
        my_file = self.request.FILES.get('my_file', None)
        file_type = self.request.data.get('file_type', None)
        user = self.request.iser
        if not my_file:
            return Response({'error': '请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        if str(file_type) not in ['1', '2', '3']:
            return Response({'error': '上传类型有误'}, status=status.HTTP_400_BAD_REQUEST)
        type_excel = my_file.name.split('.')[-1]
        file_contents = my_file.read()
        if type_excel not in ['xlsx', 'xls']:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            wb = xlrd.open_workbook(filename=None, file_contents=file_contents)  # 关键点在于这里
        except:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        table = wb.sheets()[0]
        nrows = table.nrows
        title = table.row_values(0)
        if str(file_type) == '1':
            if '员工编码' not in title:
                return Response({'error': '表格字段缺少"员工编码"'}, status=status.HTTP_400_BAD_REQUEST)
            if '子码编码' not in title:
                return Response({'error': '表格字段缺少"子码编码"'}, status=status.HTTP_400_BAD_REQUEST)
            if '员工码下载地址' not in title:
                return Response({'error': '表格字段缺少"子码编码"'}, status=status.HTTP_400_BAD_REQUEST)
        elif str(file_type) == '3':
            if '日期' not in title:
                return Response({'error': '表格字段缺少"日期"'}, status=status.HTTP_400_BAD_REQUEST)
            if '当日新增银行首绑量' not in title:
                return Response({'error': '表格字段缺少"当日新增银行首绑量"'}, status=status.HTTP_400_BAD_REQUEST)
            if '当日引导绑卡数' not in title:
                return Response({'error': '表格字段缺少"当日引导绑卡数"'}, status=status.HTTP_400_BAD_REQUEST)
            if '子码编码' not in title:
                return Response({'error': '表格字段缺少"子码编码"'}, status=status.HTTP_400_BAD_REQUEST)
        else:
            if '绑卡时间' not in title:
                return Response({'error': '表格字段缺少"绑卡时间"'}, status=status.HTTP_400_BAD_REQUEST)
            if '绑卡银行卡号' not in title:
                return Response({'error': '表格字段缺少"绑卡银行卡号"'}, status=status.HTTP_400_BAD_REQUEST)
            if '是否银行首绑' not in title:
                return Response({'error': '表格字段缺少"是否银行首绑"'}, status=status.HTTP_400_BAD_REQUEST)
            if '子码编码' not in title:
                return Response({'error': '表格字段缺少"子码编码"'}, status=status.HTTP_400_BAD_REQUEST)
            if '绑卡类型' not in title:
                return Response({'error': '表格字段缺少"绑卡类型"'}, status=status.HTTP_400_BAD_REQUEST)
        file_name = f'{my_file.name.replace(type_excel, "").replace(".", "")}_{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}.{type_excel}'
        file = pic_upload(file_contents, file_name)
        check_quest = MerchantBankQRCodeQuest.objects.filter(file_status=2)
        if check_quest:
            file_status = 1
        else:
            file_status = 2
        quest = MerchantBankQRCodeQuest.objects.create(create_user_id=user.id, file=file, file_type=file_type, file_status=file_status, total_line=nrows-1)
        if file_status == 2:
            operation_merchant_bank_quest.delay({'id': quest.id})
            # operation_merchant_bank_quest({'id': quest.id})
        return Response({'success': '上传文件成功'}, status=status.HTTP_200_OK)


class MerchantBankQRCodeModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = MerchantBankQRCodeFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('code_id', 'business_no', 'merchant_name')

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        sort = self.request.query_params.get('sort', '2')
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = MerchantBankQRCode.objects.all()
        elif level.level == 1:
            queryset = MerchantBankQRCode.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = MerchantBankQRCode.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = MerchantBankQRCode.objects.filter(level__district_id=level.id)
        else:
            queryset = MerchantBankQRCode.objects.filter(level__branch_id=level.id)
        if str(sort) == '1':
            queryset = queryset.order_by('visit', 'id')
        if str(sort) == '2':
            queryset = queryset.order_by('-visit', 'id')
        return queryset

    def get_serializer_class(self):
        if self.action == 'list':
            return MerchantBankQRCodeListModelSerializer
        else:
            return MerchantBankQRCodeRetrieveModelSerializer

    def export_bankqrcode(self, request):
        user = self.request.iser
        level_id = self.request.query_params.get('level_id', None)
        search = self.request.query_params.get('search', None)
        is_download = self.request.query_params.get('is_download', None)
        level = user.level
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE id != 0'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if is_download:
            condition = condition + f' AND is_download in ({is_download})'
        if search:
            condition = condition + f' AND (code_id LIKE "%{search}%" OR business_no LIKE "%{search}%" OR merchant_name LIKE "%{search}%")'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        # try:
        #     sql = f'''
        #     SELECT i.*,j.yesterday_first_number,yesterday_number FROM (SELECT g.*,h.last_month_number,last_month_first_number FROM (SELECT e.*,f.this_month_number,this_month_first_number FROM (SELECT c.*,d.total_first_number,total_number FROM (SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT id,code_id,business_no,merchant_name,level_id,is_download,visit FROM tb_merchant_bank_qrcode {condition})AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id)AS c LEFT JOIN (SELECT bank_qrcode_id,COUNT(id)AS total_number,SUM(is_first)AS total_first_number FROM tb_merchant_bank_qrcode_bind_data GROUP BY bank_qrcode_id)AS d ON c.id=d.bank_qrcode_id)AS e LEFT JOIN (SELECT bank_qrcode_id,COUNT(id) AS this_month_number,SUM(is_first)AS this_month_first_number FROM tb_merchant_bank_qrcode_bind_data WHERE DATE(bind_time) >= CURDATE() - INTERVAL DAY(CURDATE()) - 1 DAY AND DATE(bind_time) < DATE_ADD(CURDATE(), INTERVAL 1 MONTH) GROUP BY bank_qrcode_id)AS f ON e.id=f.bank_qrcode_id)AS g LEFT JOIN (SELECT bank_qrcode_id,COUNT(id) AS last_month_number,SUM(is_first)AS last_month_first_number FROM tb_merchant_bank_qrcode_bind_data WHERE YEAR(bind_time) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))AND MONTH(bind_time) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))AND DAY(bind_time) >= 1 GROUP BY bank_qrcode_id)AS h ON g.id=h.bank_qrcode_id)AS i LEFT JOIN (SELECT bank_qrcode_id,COUNT(id) AS yesterday_number,SUM(is_first)AS yesterday_first_number FROM tb_merchant_bank_qrcode_bind_data WHERE bind_time >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND bind_time < CURDATE() GROUP BY bank_qrcode_id)AS j ON i.id=j.bank_qrcode_id;
        #     '''
        #     cursor.execute(sql)
        # except:
        #     sql = f'''
        #     SELECT i.*,j.yesterday_first_number,yesterday_number FROM (SELECT g.*,h.last_month_number,last_month_first_number FROM (SELECT e.*,f.this_month_number,this_month_first_number FROM (SELECT c.*,d.total_first_number,total_number FROM (SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT id,code_id,business_no,merchant_name,level_id,is_download,visit FROM tb_merchant_bank_qrcode)AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id)AS c LEFT JOIN (SELECT bank_qrcode_id,COUNT(id)AS total_number,SUM(is_first)AS total_first_number FROM tb_merchant_bank_qrcode_bind_data GROUP BY bank_qrcode_id)AS d ON c.id=d.bank_qrcode_id)AS e LEFT JOIN (SELECT bank_qrcode_id,COUNT(id) AS this_month_number,SUM(is_first)AS this_month_first_number FROM tb_merchant_bank_qrcode_bind_data WHERE DATE(bind_time) >= CURDATE() - INTERVAL DAY(CURDATE()) - 1 DAY AND DATE(bind_time) < DATE_ADD(CURDATE(), INTERVAL 1 MONTH) GROUP BY bank_qrcode_id)AS f ON e.id=f.bank_qrcode_id)AS g LEFT JOIN (SELECT bank_qrcode_id,COUNT(id) AS last_month_number,SUM(is_first)AS last_month_first_number FROM tb_merchant_bank_qrcode_bind_data WHERE YEAR(bind_time) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))AND MONTH(bind_time) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))AND DAY(bind_time) >= 1 GROUP BY bank_qrcode_id)AS h ON g.id=h.bank_qrcode_id)AS i LEFT JOIN (SELECT bank_qrcode_id,COUNT(id) AS yesterday_number,SUM(is_first)AS yesterday_first_number FROM tb_merchant_bank_qrcode_bind_data WHERE bind_time >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND bind_time < CURDATE() GROUP BY bank_qrcode_id)AS j ON i.id=j.bank_qrcode_id;
        #     '''
        #     cursor.execute(sql)
        try:
            sql = f'''SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT * FROM tb_merchant_bank_qrcode {condition})AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id;'''
            cursor.execute(sql)
        except:
            sql = f'''SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT * FROM tb_merchant_bank_qrcode)AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id;'''
            cursor.execute(sql)
        ziduan = ['ID', '子码编码', '微邮付商户号', '商户名称', '所属市公司', '所属区县', '所属网点', '机构号', '是否下载', '浏览次数', '累计绑卡数', '累计首绑数']
        cols = ['id', 'code_id', 'business_no', 'merchant_name', 'city_name', 'district_name', 'branch_name', 'account_nine', 'is_download', 'visit', 'total_number', 'total_first_number']
        response = StreamingHttpResponse(self.get_bankqrcode_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('商户银行卡绑卡统计.csv'))
        return response

    def get_bankqrcode_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        IS_DOWNLOAD_ENUM = {
            0: '否',
            1: '是'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col in ['yesterday_number', 'yesterday_first_number', 'this_month_number', 'this_month_first_number', 'last_month_number', 'last_month_first_number', 'total_number', 'total_first_number']:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '0')
                elif col == 'is_download':
                    tmp_str += '"%s",' % (IS_DOWNLOAD_ENUM[row[col]])
                elif col == 'code_id':
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class MerchantBankQRCodeBindDataModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = MerchantBankQRCodeBindDataFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('code_id', 'business_no', 'merchant_name')

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = MerchantBankQRCodeBindData.objects.all()
        elif level.level == 1:
            queryset = MerchantBankQRCodeBindData.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = MerchantBankQRCodeBindData.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = MerchantBankQRCodeBindData.objects.filter(level__district_id=level.id)
        else:
            queryset = MerchantBankQRCodeBindData.objects.filter(level__branch_id=level.id)
        return queryset.order_by('-id')

    def get_serializer_class(self):
        return MerchantBankQRCodeBindDataListModelSerializer

    def export_bankqrcodedata(self, request):
        user = self.request.iser
        level_id = self.request.query_params.get('level_id', None)
        card_type = self.request.query_params.get('card_type', None)
        is_first = self.request.query_params.get('is_first', None)
        bank_qrcode_id = self.request.query_params.get('bank_qrcode_id', None)
        bind_time_begin = self.request.query_params.get('bind_time_begin', None)
        bind_time_end = self.request.query_params.get('bind_time_end', None)
        search = self.request.query_params.get('search', None)
        level = user.level
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE id != 0'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if search:
            condition = condition + f' AND (code_id LIKE "%{search}%" OR business_no LIKE "%{search}%" OR merchant_name LIKE "%{search}%")'
        if card_type:
            condition = condition + f' AND card_type in ({card_type})'
        if is_first:
            condition = condition + f' AND is_first in ({is_first})'
        if bank_qrcode_id:
            condition = condition + f' AND bank_qrcode_id in ({bank_qrcode_id})'
        if bind_time_begin:
            condition = condition + f' AND bind_time >= "{bind_time_begin + " 00:00:00"}"'
        if bind_time_end:
            condition = condition + f' AND bind_time <= "{bind_time_end + " 23:59:59"}"'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT id,code_id,business_no,merchant_name,DATE_FORMAT(bind_time,'%Y-%m-%d %H:%i:%S')AS bind_time,is_first,card_type,card_number,level_id FROM tb_merchant_bank_qrcode_bind_data {condition})AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id;
            '''
            cursor.execute(sql)
        except:
            sql = f'''
            SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT id,code_id,business_no,merchant_name,DATE_FORMAT(bind_time,'%Y-%m-%d %H:%i:%S')AS bind_time,is_first,card_type,card_number,level_id FROM tb_merchant_bank_qrcode_bind_data)AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id;
            '''
            cursor.execute(sql)
        ziduan = ['ID', '子码编码', '微邮付商户号', '商户名称', '所属市公司', '所属区县', '所属网点', '机构号', '绑卡类型', '绑卡时间', '绑卡银行卡号', '是否银行首绑']
        cols = ['id', 'code_id', 'business_no', 'merchant_name', 'city_name', 'district_name', 'branch_name', 'account_nine', 'bind_time', 'card_type', 'bind_time', 'card_number', 'is_first']
        response = StreamingHttpResponse(self.get_bankqrcodedata_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('商户银行卡绑卡明细.csv'))
        return response

    def get_bankqrcodedata_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        IS_FIRST_ENUM = {
            0: '否',
            1: '是'
        }
        CARD_TYPE_ENUM = {
            1: '储蓄卡',
            2: '信用卡'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'is_first':
                    tmp_str += '"%s",' % (IS_FIRST_ENUM[row[col]])
                elif col == 'card_type':
                    tmp_str += '"%s",' % (CARD_TYPE_ENUM[row[col]])
                elif col in ['idcode', 'payment_no']:
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class MerchantBankQPCodeStatisticsModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = MErchantBankQPCodeStatisticsFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('code_id', 'business_no', 'merchant_name')

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = MErchantBankQPCodeStatistics.objects.all()
        elif level.level == 1:
            queryset = MErchantBankQPCodeStatistics.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = MErchantBankQPCodeStatistics.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = MErchantBankQPCodeStatistics.objects.filter(level__district_id=level.id)
        else:
            queryset = MErchantBankQPCodeStatistics.objects.filter(level__branch_id=level.id)
        return queryset.order_by('-id')

    def get_serializer_class(self):
        return MErchantBankQPCodeStatisticsListModelSerializer

    def export_bankqrcodestatistics(self, request):
        user = self.request.iser
        level_id = self.request.query_params.get('level_id', None)
        bank_qrcode_id = self.request.query_params.get('bank_qrcode_id', None)
        bind_time_begin = self.request.query_params.get('bind_time_begin', None)
        bind_time_end = self.request.query_params.get('bind_time_end', None)
        search = self.request.query_params.get('search', None)
        level = user.level
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE id != 0'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if search:
            condition = condition + f' AND (code_id LIKE "%{search}%" OR business_no LIKE "%{search}%" OR merchant_name LIKE "%{search}%")'
        if bank_qrcode_id:
            condition = condition + f' AND bank_qrcode_id in ({bank_qrcode_id})'
        if bind_time_begin:
            condition = condition + f' AND bind_time >= "{bind_time_begin + " 00:00:00"}"'
        if bind_time_end:
            condition = condition + f' AND bind_time <= "{bind_time_end + " 23:59:59"}"'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT id,code_id,business_no,merchant_name,DATE_FORMAT(bind_time,'%Y-%m-%d %H:%i:%S')AS bind_time,level_id,total_number,total_first_number FROM tb_merchant_bank_qrcode_statistics {condition})AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id;
            '''
            cursor.execute(sql)
        except:
            sql = f'''
            SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT id,code_id,business_no,merchant_name,DATE_FORMAT(bind_time,'%Y-%m-%d %H:%i:%S')AS bind_time,level_id,total_number,total_first_number FROM tb_merchant_bank_qrcode_statistics)AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id;
            '''
            cursor.execute(sql)
        ziduan = ['ID', '子码编码', '微邮付商户号', '商户名称', '所属市公司', '所属区县', '所属网点', '机构号', '绑卡时间', '当日引导绑卡数', '当日新增银行首绑量']
        cols = ['id', 'code_id', 'business_no', 'merchant_name', 'city_name', 'district_name', 'branch_name', 'account_nine', 'bind_time', 'total_number', 'total_first_number']
        response = StreamingHttpResponse(self.get_bankqrcodestatistics_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('商户银行卡绑卡统计.csv'))
        return response

    def get_bankqrcodestatistics_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        IS_FIRST_ENUM = {
            0: '否',
            1: '是'
        }
        CARD_TYPE_ENUM = {
            1: '储蓄卡',
            2: '信用卡'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'is_first':
                    tmp_str += '"%s",' % (IS_FIRST_ENUM[row[col]])
                elif col == 'card_type':
                    tmp_str += '"%s",' % (CARD_TYPE_ENUM[row[col]])
                elif col in ['idcode', 'payment_no']:
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"








